By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE Table MyTable(Date DATETIME, Amount INT)
INSERT MyTable VALUES
('27-Sep-2021',1),
('28-Sep-2021',13),
('29-Sep-2021',15),
('30-Sep-2021',20),
('1-Oct-2021',9),
('2-Oct-2021',20),
('3-Oct-2021',8),
('4-Oct-2021',2),
('5-Oct-2021',9),
('6-Oct-2021',11),
('7-Oct-2021',15),
('8-Oct-2021',8),
('9-Oct-2021',16),
('10-Oct-2021',3),
('11-Oct-2021',3),
('12-Oct-2021',18)
16 rows affected
--Monday=2
;WITH Grouped AS
(
SELECT *,
IsMondayGroup = SUM(CASE WHEN DATEPART(WEEKDAY,Date) = 2 THEN 1 ELSE 0 END) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING)
FROM
MyTable
)
SELECT
Date,Amount,
GroupSum = SUM(Amount) OVER(PARTITION BY IsMondayGroup ORDER BY Date)
FROM
Grouped
| Date | Amount | GroupSum |
|---|---|---|
| 2021-09-27 00:00:00.000 | 1 | 1 |
| 2021-09-28 00:00:00.000 | 13 | 14 |
| 2021-09-29 00:00:00.000 | 15 | 29 |
| 2021-09-30 00:00:00.000 | 20 | 49 |
| 2021-10-01 00:00:00.000 | 9 | 58 |
| 2021-10-02 00:00:00.000 | 20 | 78 |
| 2021-10-03 00:00:00.000 | 8 | 86 |
| 2021-10-04 00:00:00.000 | 2 | 2 |
| 2021-10-05 00:00:00.000 | 9 | 11 |
| 2021-10-06 00:00:00.000 | 11 | 22 |
| 2021-10-07 00:00:00.000 | 15 | 37 |
| 2021-10-08 00:00:00.000 | 8 | 45 |
| 2021-10-09 00:00:00.000 | 16 | 61 |
| 2021-10-10 00:00:00.000 | 3 | 64 |
| 2021-10-11 00:00:00.000 | 3 | 3 |
| 2021-10-12 00:00:00.000 | 18 | 21 |