clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
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. 818551 fiddles created (9198 in the last week).

CREATE TABLE sch AS SELECT ctr_id::int , mth::date , pmt_amt::numeric FROM ( VALUES ( 1 , '2019-01-01' , 145.0 ) , ( 1 , '2019-02-01' , 145.0 ) , ( 1 , '2019-03-01' , 145.0 ) , ( 1 , '2019-04-01' , 145.0 ) , ( 1 , '2019-05-01' , 145.0 ) , ( 1 , '2019-06-01' , 145.0 ) ) AS sch ( ctr_id , mth , pmt_amt ) ;
6 rows affected
 hidden batch(es)


CREATE TABLE pmt AS SELECT ctr_id::int , dt::date , amt::numeric FROM ( VALUES ( 1 , '2019-01-04' , 145.0 ) , ( 1 , '2019-02-01' , 145.0 ) , ( 1 , '2019-03-01' , 145.0 ) , ( 1 , '2019-03-29' , 145.0 ) , ( 1 , '2019-05-03' , 145.0 ) , ( 1 , '2019-06-07' , 145.0 ) ) AS sch ( ctr_id , dt , amt ) ;
6 rows affected
 hidden batch(es)


select ctr_id, mth, cumulative_pmt_amt, coalesce(cumulative_amt, 0) as cumulative_amt , coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance from ( select ctr_id, mth , sum(pmt_amt) over (partition by ctr_id order by mth) as cumulative_pmt_amt , ( select sum(amt) from pmt y where x.ctr_id = y.ctr_id and y.dt <= x.mth ) as cumulative_amt from sch x ) as t;
ctr_id mth cumulative_pmt_amt cumulative_amt balance
1 2019-01-01 145.0 0 -145.0
1 2019-02-01 290.0 290.0 0.0
1 2019-03-01 435.0 435.0 0.0
1 2019-04-01 580.0 580.0 0.0
1 2019-05-01 725.0 580.0 -145.0
1 2019-06-01 870.0 725.0 -145.0
 hidden batch(es)


DROP TABLE pmt;
 hidden batch(es)


CREATE TABLE pmt AS SELECT ctr_id::int , dt::date , amt::numeric FROM ( VALUES ( 1 , '2019-01-01' , 145.0 ) , ( 1 , '2019-05-01' , 435.0 ) , ( 1 , '2019-05-03' , 145.0 ) , ( 1 , '2019-06-01' , 145.0 ) ) AS sch ( ctr_id , dt , amt ) ;
4 rows affected
 hidden batch(es)


select ctr_id, mth, cumulative_pmt_amt, coalesce(cumulative_amt, 0) as cumulative_amt , coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance from ( select ctr_id, mth , sum(pmt_amt) over (partition by ctr_id order by mth) as cumulative_pmt_amt , ( select sum(amt) from pmt y where x.ctr_id = y.ctr_id and y.dt <= x.mth ) as cumulative_amt from sch x ) as t;
ctr_id mth cumulative_pmt_amt cumulative_amt balance
1 2019-01-01 145.0 145.0 0.0
1 2019-02-01 290.0 145.0 -145.0
1 2019-03-01 435.0 145.0 -290.0
1 2019-04-01 580.0 145.0 -435.0
1 2019-05-01 725.0 580.0 -145.0
1 2019-06-01 870.0 870.0 0.0
 hidden batch(es)