SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



select cast('2023-01-04' as date)


(No column name)


2023-01-04


with sales(id, colour, payment_date, amount) as (
	select 1, 'red', cast( '2023-01-04' as date), 2  union all
	select 1, 'green', cast( '2023-01-04'as date), 5  union all
	select 2, 'green', cast( '2023-01-04'as date), 1  union all
	select 2, 'green', cast('2023-02-04'as date), 1  union all
	select 2, 'green', cast( '2023-03-04'as date), 1  union all
	select 2, 'green', cast( '2023-04-04' as date), 1 
),
report_dates(dat) as (
	select cast( '2022-12-31' as date) union all
	select cast( '2023-01-31' as date) union all
	select cast( '2023-02-28' as date) union all
	select cast( '2023-03-31' as date) union all
	select cast( '2023-04-30' as date) union all
	select cast( '2023-05-31' as date) union all
	select cast( '2023-06-30'as date) 
),
stats as (
	select s.id, s.colour, r.dat, 
		sum(s.amount) over(partition by s.colour, s.id order by r.dat) as cum_amount
	from (select dat, lag(dat) over(order by dat) as from_dat from report_dates) r
	join sales s on s.payment_date between coalesce(r.from_dat, cast('0001-01-01'as date)) and r.dat
)
select * from (
	select s.id, s.colour, r.dat, s.cum_amount
	from report_dates r
	join stats s on s.dat <= r.dat
	
	union all
	
	select s.id, s.colour, r.dat, 0
	from report_dates r
	, (select distinct id, colour from sales) s
	where not exists(select 1 from stats st where st.dat <= r.dat and st.id = s.id and st.colour = s.colour) 
) t
order by id, colour desc, dat
;


id
colour
dat
cum_amount


1
red
2022-12-31
0

1
red
2023-01-31
2

1
red
2023-02-28
2

1
red
2023-03-31
2

1
red
2023-04-30
2

1
red
2023-05-31
2

1
red
2023-06-30
2

1
green
2022-12-31
0

1
green
2023-01-31
5

1
green
2023-02-28
5

1
green
2023-03-31
5

1
green
2023-04-30
5

1
green
2023-05-31
5

1
green
2023-06-30
5

2
green
2022-12-31
0

2
green
2023-01-31
1

2
green
2023-02-28
1

2
green
2023-02-28
2

2
green
2023-03-31
2

2
green
2023-03-31
3

2
green
2023-03-31
1

2
green
2023-04-30
1

2
green
2023-04-30
3

2
green
2023-04-30
2

2
green
2023-04-30
4

2
green
2023-05-31
4

2
green
2023-05-31
2

2
green
2023-05-31
3

2
green
2023-05-31
1

2
green
2023-06-30
1

2
green
2023-06-30
3

2
green
2023-06-30
2

2
green
2023-06-30
4

id	colour	dat	cum_amount
1	red	2022-12-31	0
1	red	2023-01-31	2
1	red	2023-02-28	2
1	red	2023-03-31	2
1	red	2023-04-30	2
1	red	2023-05-31	2
1	red	2023-06-30	2
1	green	2022-12-31	0
1	green	2023-01-31	5
1	green	2023-02-28	5
1	green	2023-03-31	5
1	green	2023-04-30	5
1	green	2023-05-31	5
1	green	2023-06-30	5
2	green	2022-12-31	0
2	green	2023-01-31	1
2	green	2023-02-28	1
2	green	2023-02-28	2
2	green	2023-03-31	2
2	green	2023-03-31	3
2	green	2023-03-31	1
2	green	2023-04-30	1
2	green	2023-04-30	3
2	green	2023-04-30	2
2	green	2023-04-30	4
2	green	2023-05-31	4
2	green	2023-05-31	2
2	green	2023-05-31	3
2	green	2023-05-31	1
2	green	2023-06-30	1
2	green	2023-06-30	3
2	green	2023-06-30	2
2	green	2023-06-30	4