Vryjdfkk

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 

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