1

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 
  • Why are you overlapping dates? – McNets Jan 9 at 11:50
  • @McNets l was given the data as is my task is to retrieve the price based on the closest price to EndDate – Billy Watsy Jan 9 at 11:54
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

  • l tested your query its simple & works , thanks – Billy Watsy Jan 9 at 14:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.