clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591646 fiddles created (45705 in the last week).

create table sample_data ( document_no int, "type" char(2), year_month char(7), accty char(1), account char(1), gl_account int, doc_date date, amt numeric(9, 2), pstng_date date, entry_date date );
 hidden batch(es)


insert into sample_data values (100014096, 'AB', '2019/01', 'D', 'A', 123456, '12/1/2019', 75008, '12/1/2019', '12/1/2019'), (100014096, 'AB', '2019/02', 'D', 'A', 123457, '12/2/2019', 5008, '12/2/2019', '12/2/2019'), (100014098, 'AB', '2019/03', 'D', 'B', 123458, '12/3/2019', 93745, '12/3/2019', '12/3/2019'), (100014098, 'AB', '2019/04', 'D', 'B', 123459, '12/4/2019', 3745, '12/4/2019', '12/4/2019'), (100014106, 'AB', '2019/05', 'D', 'C', 123460, '1/5/2019', 2434047, '1/5/2019', '1/5/2019'), (100014106, 'AB', '2019/06', 'D', 'C', 123461, '1/6/2019', 434047, '1/6/2019', '1/6/2019'), (100015139, 'AB', '2019/06', 'D', 'C', 123462, '1/6/2019', 11000, '1/6/2019', '1/6/2019'), (100015987, 'AB', '2019/06', 'D', 'D', 123463, '1/6/2019', -22124.85, '1/6/2019', '1/6/2019');
8 rows affected
 hidden batch(es)


SELECT b.file_date, SUM(a.amt) AS amt, SUM(SUM(a.amt)) OVER(ORDER BY b.file_date ROWS UNBOUNDED PRECEDING) AS rollover FROM sample_data AS a INNER JOIN LATERAL (SELECT TO_DATE(a.year_month, 'yyyy/mm') AS file_date) AS b ON TRUE WHERE a."type" <> 'DZ' AND b.file_date >= DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 YEAR 6 MONTHS' GROUP BY b.file_date;
file_date amt rollover
2019-01-01 75008.00 75008.00
2019-02-01 5008.00 80016.00
2019-03-01 93745.00 173761.00
2019-04-01 3745.00 177506.00
2019-05-01 2434047.00 2611553.00
2019-06-01 422922.15 3034475.15
 hidden batch(es)