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

Question

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.

Answer 1

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