I am trying to create a price history log based on the following dataset. I have a record for each day that includes a price, which could be repeated over different periods. See the input below:
| Price | Date |
| ------------ | ------------ |
| 20 | 2023-01-01 |
| 20 | 2023-01-02 |
| 19 | 2023-01-03 |
| 19 | 2023-01-04 |
| 20 | 2023-01-05 |
| 20 | 2023-01-06 |
I would like to summarize into a price history table like this:
Price | Start Date | End Date |
---|---|---|
20 | 2023-01-01 | 2023-01-02 |
19 | 2023-01-03 | 2023-01-04 |
20 | 2023-01-05 | 2023-01-06 |
Can anyone point me in the right direction? I have been able to create the ranges, but getting tripped up by the potential for the same price coming up for multiple ranges, so that messes up the grouping.
1条答案
按热度按时间vaqhlq811#
I took your example data and turned it in to reproduceable DML/DDL which is really helpful for these sorts of questions. I added a ProductID column, and a few extra rows of data to demonstrate how to do it with multiple IDs. I used table variables because there's less to clean up, but that's just personal preference.
Using this, we can query using a recursive common table expression to get the results you're looking for. To separate the ranges I used a LAG windowed function to flag if this row belongs to the same range as the previous one.