Price Change History in SQL Server

Question

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.

When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) [mre] [ask] [Help] — philipxy, Aug 05 '22 at 07:50

score 4 · Accepted Answer · answered Aug 05 '22 at 07:12

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;

Price Change History in SQL Server

1 Answers1

Demo