Cumulative amount by report dates

Question

I have two tables

sales

id	colour	payment_date	amount
1	red	2023-01-04	2
1	green	2023-01-04	5
2	green	2023-01-04	1
2	green	2023-02-04	1
2	green	2023-03-04	1
2	green	2023-04-04	1

report_dates

date
2022-12-31
2023-01-31
2023-02-28
2023-03-31
2023-04-30
2023-05-31
2023-06-30

My aim is cumulative amount by report dates

I get cumulative amount by EOMONTH of payment_dates

SELECT
    sales.id  
    ,EOMONTH(sales.payment_date)  
    ,sales.colour
    ,SUM(sales.amount) OVER (PARTITION BY sales.id, sales.colour ORDER BY EOMONTH(sales.payment_date)) 
FROM sales
ORDER BY pl.AGREEMENT_RK, pl.OPER_DATE

How to join with report dates and get report like this? EDITED: report dates before sales date doesn't needed

id	colour	date	cum_amount
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	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	2023-01-31	1
2	green	2023-02-28	2
2	green	2023-03-31	3
2	green	2023-04-30	4
2	green	2023-05-31	4
2	green	2023-06-30	4

Answer 1

Try by calculating the SUM in a separate CTE and eventually UNION with the missing ones:

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
;

https://dbfiddle.uk/ILZn5lFc

Answer 2

This solution works, but very slow on real data (> 10mln rows of sales)

SELECT
    sales.id
    ,sales.colour
    ,report_dates.date
    ,SUM(sales.amount)
FROM report_dates
LEFT JOIN sales ON EOMONTH(sales.payment_date) <= report_dates.date
GROUP BY sales.id, sales.colour, report_dates.date
ORDER BY sales.id,  sales.colour, report_dates.date