How to generate date ranges from a table with daily records in SQL

z4bn682m  于 2023-02-28  发布在  其他
关注(0)|答案(1)|浏览(127)

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:

PriceStart DateEnd Date
202023-01-012023-01-02
192023-01-032023-01-04
202023-01-052023-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.

vaqhlq81

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.

DECLARE @PriceHistory TABLE (ProductID BIGINT, Price DECIMAL (10,2), Date DATE)
INSERT INTO @PriceHistory (ProductID, Price, Date) VALUES
(1, 20, '2023-01-01'), (1, 20, '2023-01-02'), (1, 20, '2023-01-03'), (1, 19, '2023-01-04'),
(1, 20, '2023-01-05'), (1, 20, '2023-01-06'), (1, 20, '2023-01-07'), (1, 20, '2023-01-08'),
(2, 30, '2023-01-01'), (2, 15, '2023-01-02'), (2, 15, '2023-01-03'), (2, 19, '2023-01-04'),
(2, 19, '2023-01-05'), (2, 20, '2023-01-06'), (2, 20, '2023-01-07'), (2, 20, '2023-01-08');

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.

;WITH base AS (
SELECT ProductID, Price, Date, CASE WHEN LAG(Price,1) OVER (PARTITION BY ProductID ORDER BY Date) = Price THEN 1 ELSE 0 END AS InRange
  FROM @PriceHistory
), Ranges AS (
SELECT ProductID, Price, Date AS StartDate, Date AS EndDate
  FROM base 
 WHERE InRange = 0
UNION ALL
SELECT a.ProductID, a.Price, a.StartDate, r.Date AS EndDate
  FROM Ranges a
    INNER JOIN Base r
      ON a.ProductID = r.ProductID
      AND a.EndDate = DATEADD(DAY,-1,r.Date)
      AND a.Price = r.Price
)

SELECT ProductID, Price, StartDate, MAX(EndDate) AS EndDate, DATEDIFF(DAY,StartDate, MAX(EndDate))+1 AS Duration
  FROM Ranges
 GROUP BY ProductID, Price, StartDate
 ORDER BY Ranges.ProductID, Ranges.StartDate;
ProductIDPriceStartDateEndDateDuration
120.002023-01-012023-01-033
119.002023-01-042023-01-041
120.002023-01-052023-01-084
230.002023-01-012023-01-011
215.002023-01-022023-01-032
219.002023-01-042023-01-052
220.002023-01-062023-01-083

相关问题