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;
