clear markdown feedback
clear markdown feedback
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;
Id startDate endDate sumValue
1 2021-01-01 2021-01-03 300
1 2021-12-29 2021-12-31 300
2 2021-01-01 2021-01-03 300
2 2021-06-01 2021-06-03 300
 hidden batch(es)