How to collapse rows with individual, contiguous dates into single rows with date range start and end 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.
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