By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798808 fiddles created (41848 in the last week).
CREATE TABLE test (Id INT, xDate DATE, Value INT);
INSERT INTO test VALUES
(1, '01/01/2021', 100),
(1, '01/02/2021', 100),
(1, '01/03/2021', 100),
(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);
SELECT * FROM test;
Id
xDate
Value
1
2021-01-01
100
1
2021-01-02
100
1
2021-01-03
100
1
2021-12-29
100
1
2021-12-30
100
1
2021-12-31
100
2
2021-01-01
100
2
2021-01-02
100
2
2021-01-03
100
2
2021-06-01
100
2
2021-06-02
100
2
2021-06-03
100
…
hidden batch(es)
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;
