3

Given a data set like this:

Id Date Value
1 01/01/2021 100
1 01/02/2021 100
1 01/03/2021 100
... ... (rows omitted for brevity) ...
1 12/29/2021 100
1 12/30/2021 100
1 12/31/2021 100
2 01/01/2021 100
2 01/02/2021 100
2 01/03/2021 100
2 06/01/2021 100
2 06/02/2021 100
2 06/03/2021 100

Is it possible to write a query to return a result set where each row is a contiguous date range, grouped by the Id column? Like this:

Id Start Date End Date Sum of Value
1 01/01/2021 12/31/2021 36500
2 01/01/2021 01/03/2021 300
2 06/01/2021 06/03/2021 300

I'm imagining it will be some combination of window functions and group bys, but I'm not even sure it's possible and didn't want to fumble towards a solution that didn't exist.

I'm using SQL Server 2017 and 2019, if that matters.

The date ranges can be long - months or years. For example, one of the collapsed rows could be a year long, coming from 365 individual rows, like in the given example.

1
  • This is a classic Gaps and Islands problem, and I've added a tag to your question to reflect that. – Andriy M Jan 13 at 9:15
0
WITH 
cte1 AS ( SELECT Id, xDate, Value,
                 CASE DATEDIFF( DAY, LAG(xDate) OVER (PARTITION BY Id ORDER BY xDate), xDate ) 
                 WHEN 1 
                 THEN 0
                 ELSE 1 END delta
          FROM test ),
cte2 AS ( SELECT Id, xDate, Value, SUM(delta) OVER (ORDER BY Id, xDate) grp
          FROM cte1 )
SELECT Id, MIN(xDate) startDate, MAX(xDate) endDate, SUM(Value) sumValue
FROM cte2
GROUP BY id, grp;

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.