Postgres 16

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

create table my_table("ID", "Date", "Invoiced",	"Amount") as values
 ('AAA','2023-01',	0,	10)
,('AAA','2023-02',	0,	15)
,('AAA','2023-03',	1,	15)
,('AAA','2023-04',	0,	10)
,('AAA','2023-05',	0,	10)
,('AAA','2023-06',	1,	10)
,('AAA','2023-07',	0,	7)
,('AAA','2023-08',	0,	7)
,('BBB','2022-05',	0,	40)
,('BBB','2022-06',	1,	20)
,('BBB','2022-07',	0,	30)
,('BBB','2022-08',	1,	30)
,('BBB','2022-09',	0,	7)
,('BBB','2022-10',	0,	7);
--I need to have the rows only per ID with the invoice months, where we sum over the previous amounts since the last invoice. What I want to have:
/*
ID	Date	Invoiced	Amount
AAA	2023-03	1	40
AAA	2023-06	1	30
BBB	2022-06	1	60
BBB	2022-08	1	60
How this can be done in postgresql? I started with the query below, but it gives not yet what I need.
*/
select * from my_table order by "ID","Date";

SELECT 14




ID
Date
Invoiced
Amount


AAA
2023-01
0
10

AAA
2023-02
0
15

AAA
2023-03
1
15

AAA
2023-04
0
10

AAA
2023-05
0
10

AAA
2023-06
1
10

AAA
2023-07
0
7

AAA
2023-08
0
7

BBB
2022-05
0
40

BBB
2022-06
1
20

BBB
2022-07
0
30

BBB
2022-08
1
30

BBB
2022-09
0
7

BBB
2022-10
0
7



SELECT 14


select "ID", "Date", "Invoiced",
  (select sum(t2."Amount")+t1."Amount" 
   from my_table t2
   where t2."ID"=t1."ID"
     and t2."Invoiced"=0
     and t2."Date"<=t1."Date"
     and t2."Date">=(select coalesce(max("Date"),'0000-00') 
                     from my_table t3
                     where t3."ID"=t1."ID"
                       and t3."Invoiced"=1 
                       and t3."Date"<t1."Date")
  ) as "Amount"
from my_table as t1
where "Invoiced"=1;


ID
Date
Invoiced
Amount


AAA
2023-03
1
40

AAA
2023-06
1
30

BBB
2022-06
1
60

BBB
2022-08
1
60



SELECT 4


--debug
select "ID", "Date", "Invoiced",
  (select array_agg(t2."Amount")||t1."Amount" from my_table t2
   where t2."ID"=t1."ID"
     and t2."Invoiced"=0
     and t2."Date"<=t1."Date"
     and t2."Date">=coalesce(
                    (select max("Date") from my_table t3
                     where t3."ID"=t1."ID"
                       and t3."Invoiced"=1 
                       and t3."Date"<t1."Date")
                    ,'0000-00') ) as "Amounts"
from my_table as t1
where "Invoiced"=1;


ID
Date
Invoiced
Amounts


AAA
2023-03
1
{10,15,15}

AAA
2023-06
1
{10,10,10}

BBB
2022-06
1
{40,20}

BBB
2022-08
1
{30,30}



SELECT 4

ID	Date	Invoiced	Amount
AAA	2023-01	0	10
AAA	2023-02	0	15
AAA	2023-03	1	15
AAA	2023-04	0	10
AAA	2023-05	0	10
AAA	2023-06	1	10
AAA	2023-07	0	7
AAA	2023-08	0	7
BBB	2022-05	0	40
BBB	2022-06	1	20
BBB	2022-07	0	30
BBB	2022-08	1	30
BBB	2022-09	0	7
BBB	2022-10	0	7

ID	Date	Invoiced	Amounts
AAA	2023-03	1	{10,15,15}
AAA	2023-06	1	{10,10,10}
BBB	2022-06	1	{40,20}
BBB	2022-08	1	{30,30}