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.