Price Change History in SQL Server

I have a table in SQL Server with sales price data of items on different dates like this:

Item Date Price
1 2021-05-01 200
1 2021-06-11 210
1 2021-06-27 225
1 2021-08-01 250
2 2021-02-10 600
2 2021-04-21 650
2 2021-06-17 675
2 2021-07-23 700

I’m creating a table that specifies the start and end date of prices as below:

Item DateStart Price DateEnd
1 2021-05-01 200 2021-06-10
1 2021-06-11 210 2021-06-26
1 2021-06-27 225 2021-07-31
1 2021-08-01 250 Today date
2 2021-02-10 600 2021-04-20
2 2021-04-21 650 2021-06-16
2 2021-06-17 675 2021-07-22
2 2021-07-23 700 Today date

As you can see, the end date is one day less than the next price change date. I also have a calendar table called "DimDates" with one row per day. I had hoped to use joins but it doesn’t do what I thought it would do. Any suggestions on how to write the query? I’m using SQL Server 2016.

>Solution :

We can use LEAD() here along with DATEADD():

WITH cte AS (
    SELECT *, DATEADD(day, -1, LEAD(Date, 1, GETDATE())
                                   OVER (PARTITION BY Item
                                         ORDER BY Date)) AS LastDate
    FROM yourTable
)

SELECT Item, Date AS DateStart, Price, LastDate AS DateEnd
FROM cte
ORDER BY Item, Date;

screen capture from demo link below

Demo

Leave a Reply