Sql-server – How to collapse rows with individual, contiguous dates into single rows with date range start and end

gaps-and-islandsgroup bysql serversql-server-2017sql-server-2019

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.

Best Answer

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