By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Declare @YourTable Table ([Date] date,[Amount] int)
Insert Into @YourTable 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)
SET DATEFIRST 1;
Select *
,WTD = sum(Amount) over (partition by datepart(Week,Date) order by Date rows unbounded preceding)
from @YourTable
| Date | Amount | WTD |
|---|---|---|
| 2021-09-27 | 1 | 1 |
| 2021-09-28 | 13 | 14 |
| 2021-09-29 | 15 | 29 |
| 2021-09-30 | 20 | 49 |
| 2021-10-01 | 9 | 58 |
| 2021-10-02 | 20 | 78 |
| 2021-10-03 | 8 | 86 |
| 2021-10-04 | 2 | 2 |
| 2021-10-05 | 9 | 11 |
| 2021-10-06 | 11 | 22 |
| 2021-10-07 | 15 | 37 |
| 2021-10-08 | 8 | 45 |
| 2021-10-09 | 16 | 61 |
| 2021-10-10 | 3 | 64 |
| 2021-10-11 | 3 | 3 |
| 2021-10-12 | 18 | 21 |