Select within or closest to date range MSSQL

Question

Lets say we have a table

CREATE TABLE [dbo].[Product](
    [ProductId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) 

CREATE TABLE [ProductPrice]( 
    [ProductPriceId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [decimal](19, 4) NOT NULL,
    [StartDate] [datetime2](7) NOT NULL,
    [EndDate] [datetime2](7) NULL
)

And sample data

INSERT INTO ProductPrice
    ([ProductPriceId] ,[ProductId], [Price], [StartDate], [EndDate])
VALUES 
    ( 1 ,1 , 23 , '2018-12-13' , '2018-12-27'), 
    ( 2 ,1 , 26 , '2018-12-18' , '2018-12-20'), 
    ( 3 ,1 , 21 , '2018-12-10' , null), 
    ( 4 ,1 , 22 , '2018-12-28' , '2018-12-30'), 
    ( 5 ,1 , 27 , '2019-1-01' , '2019-01-18')  
;


INSERT INTO [Product]
           ([ProductId]
           ,[Name])
     VALUES
           (1 ,'Burger') ,
           (2 ,'Coke 2L') 
           ;

The productID is a foreign key to another table Product

this table has the prices for our products .

The question is assuming today's date is 2018-12-19 (19 Decemeber 2018)

How do we get the current price for date (2018-12-19) based on giving priority to EndDate is not null compared to the default date were price EndDate will be null

Question 1 How do l get price trend with desired output like below based on EndDate

thus in our case the closest date based on end date is rightful order:

   -1- EndDate 2018-12-20 ( 20 Decemeber 2018 )  ProductPriceID : 2   Price : 26
   -2- EndDate 2018-12-27 ( 27 Decemeber 2018 )  ProductPriceID : 1   Price : 23
   -3- EndDate 2018-12-30 ( 30 Decemeber 2018 )  ProductPriceID : 4   Price : 22
   -4- EndDate 2019-1-01 ( 1 January 2019 )      ProductPriceID : 5   Price : 27    
   -5- EndDate NULL                              ProductPriceID : 3   Price : 21

if there is no price within range then the default is date with null field : thus [EndDate NULL ProductPriceID : 3 Price : 21]

Question 2 is How to get curret price based on EndDate the priority mentioned above

the current desired price is : 26

but however if i do this

 /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ProductPriceId]
      ,[ProductId]
      ,[Price]
      ,[StartDate]
      ,[EndDate]
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   (
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   )
   order by p.[EndDate] asc

l get :

-----------------------------------------------------------------------
ProductPriceId  ProductId   Price   StartDate   EndDate
--------------------------------------------------------------------------
3               1           21.0000 2018-12-10  NULL
2               1           26.0000 2018-12-18  2018-12-19 
1               1           23.0000 2018-12-13  2018-12-27
4               1           22.0000 2018-12-28  2018-12-30
5               1           27.0000 2019-01-01  2019-01-18

but however null should be at the end

Question 3 : retrieving the whole thing product list with the current price :

what l tried

/****** Script for SelectTopNRows command from SSMS  ******/

;with ctaCurrentPrice As
(
  -- tried gettign current price for product 
    SELECT *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
)
Select * , 
    (
      Select Top 1 Price From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As Price ,
    (
     Select Top 1 StartDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As StartDate,
    (
     Select Top 1 EndDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As EndDate
 From Product As pro

what am getting

ProductId Name Price StartDate EndDate

1       Burger  23.0000 2018-12-13  2018-12-27
2       Coke 2L NULL    NULL        NULL

of which the desired is to be like

---

ProductId Name Price StartDate EndDate

1       Burger  26.0000 2018-12-18  2018-12-19 
2       Coke 2L NULL    NULL        NULL

Edit 2 :

l have found the solution hope

SELECT Top 1 *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
    p.[ProductId] = 1
    AND
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
   order by case when p.[EndDate] is null then 2 else 1 end, p.[EndDate]  asc 

Answer 1

Question 1 How do l get price trend with desired output like below based on EndDate

Simply use COALESCE to convert NULL date values to 31/12/9999 that is the maximum value allowed for a datetime column.

SELECT
    ProductPriceId,
    ProductId,
    Price,
    StartDate,
    EndDate
FROM
    ProductPrice
ORDER BY
    ProductId,
    COALESCE(EndDate, '99991231');

ProductPriceId | ProductId | Price   | StartDate           | EndDate            
-------------: | --------: | :------ | :------------------ | :------------------
             2 |         1 | 26.0000 | 18/12/2018 00:00:00 | 20/12/2018 00:00:00
             1 |         1 | 23.0000 | 13/12/2018 00:00:00 | 27/12/2018 00:00:00
             4 |         1 | 22.0000 | 28/12/2018 00:00:00 | 30/12/2018 00:00:00
             5 |         1 | 27.0000 | 01/01/2019 00:00:00 | 18/01/2019 00:00:00
             3 |         1 | 21.0000 | 10/12/2018 00:00:00 | null               

Question 2 is How to get curret price based on EndDate the priority mentioned above

Then use an OUTER APPLY join to get the first row that match the condition:

DECLARE @PriceDate datetime = '20181219';

SELECT
    p.ProductId,
    p.Name,
    pl.Price,
    pl.StartDate,
    pl.EndDate
FROM 
    Product p
OUTER APPLY (SELECT TOP 1
                 ProductPriceId,
                 ProductId,
                 Price,
                 StartDate,
                 EndDate
             FROM
                 ProductPrice
             WHERE
                 ProductId = p.ProductId
                 AND EndDate > @PriceDate 
             ORDER BY
                 ProductId,
                 COALESCE(EndDate, '99991231')) pl;

ProductId | Name    | Price   | StartDate           | EndDate            
--------: | :------ | :------ | :------------------ | :------------------
        1 | Burger  | 26.0000 | 18/12/2018 00:00:00 | 20/12/2018 00:00:00
        2 | Coke 2L | null    | null                | null               

db<>fiddle here