SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table data (week int, amount decimal(10,2))
insert into data values (1, 100), (2, 100), (3, 100), (4, 100),
(5, 100000), (6, 100), (7, 50000), (8, 50000), (9, 50000), (10, 100)
10 rows affected

create table bad_weeks (week int)
insert into bad_weeks values (5), (7), (8), (9)
4 rows affected

select * from bad_weeks


week


5

7

8

9


select d.week, d.amount, 
sum(case when b.week is  null then d.amount end) over ( order by d.week rows between 4 preceding and 1 preceding) prev
from data d left join bad_weeks b on d.week = b.week


week
amount
prev


1
100.00
null

2
100.00
100.00

3
100.00
200.00

4
100.00
300.00

5
100000.00
400.00

6
100.00
300.00

7
50000.00
300.00

8
50000.00
200.00

9
50000.00
100.00

10
100.00
100.00


Warning: Null value is eliminated by an aggregate or other SET operation.


select d.week, d.amount, 
sum(case when b.week is  null then d.amount end) over ( order by d.week rows between 4 preceding and 1 preceding) sum4,
count(case when b.week is  null then 1 end) over ( order by d.week rows between 4 preceding and 1 preceding) cnt4,
sum(case when b.week is  null then d.amount end) over ( order by d.week rows between 5 preceding and 1 preceding) sum5,
count(case when b.week is  null then 1 end) over ( order by d.week rows between 5 preceding and 1 preceding) cnt5
,sum(case when b.week is  null then d.amount end) over ( order by d.week rows between 6 preceding and 1 preceding) sum6,
count(case when b.week is  null then 1 end) over ( order by d.week rows between 6 preceding and 1 preceding) cnt6
from data d left join bad_weeks b on d.week = b.week


week
amount
sum4
cnt4
sum5
cnt5
sum6
cnt6


1
100.00
null
0
null
0
null
0

2
100.00
100.00
1
100.00
1
100.00
1

3
100.00
200.00
2
200.00
2
200.00
2

4
100.00
300.00
3
300.00
3
300.00
3

5
100000.00
400.00
4
400.00
4
400.00
4

6
100.00
300.00
3
400.00
4
400.00
4

7
50000.00
300.00
3
400.00
4
500.00
5

8
50000.00
200.00
2
300.00
3
400.00
4

9
50000.00
100.00
1
200.00
2
300.00
3

10
100.00
100.00
1
100.00
1
200.00
2


Warning: Null value is eliminated by an aggregate or other SET operation.

week	amount	prev
1	100.00	null
2	100.00	100.00
3	100.00	200.00
4	100.00	300.00
5	100000.00	400.00
6	100.00	300.00
7	50000.00	300.00
8	50000.00	200.00
9	50000.00	100.00
10	100.00	100.00