Monthly cumulative differences calculation in postgres

user_17673915 0
Viewed 55

I am trying to write an sql script in postgres that find cumulative difference in total price and repayment amount. I have two tables as shown below. I have gone through solution provided here but it doesn't address my question.

item table

item_id  cost_price    date_purchase
1        200           01-06-2019
2        300           10-07-2019
3        250           15-08-2019
4        400           10-09-2019

payment table
item id  payment       payment date
1          50           01-06-2019
1          40           20-06-2019
2          30           15-07-2019
1          60           17-07-2019
2          100          15-08-2019
3          90           17-08-2019
4          300          20-09-2019
1          50           25-09-2019

Expected result

Month                        Remaining amount
06_2019       (200 - 90) = 110
07_2019       (200+ 300) - (90 + 30 + 60) = 320
08_2019       (200+ 300 + 250) - (90 + 90 + 100 + 90) = 380
09_2019       (200 + 300 + 250 + 400) - (90 + 90 + 190 + 300 + 50) = 430
postgresql sql
3 Answers

You can do that by SUMs with WINDOWING function that's uses ORDER BY month. But give us the DDL of your table to be helped more...

user_126598720

Since your example ignores the item_id in the results, you can combine purchases and payments into a simple ledger and then use a window function to get a running sum:

with ledger as (
  select to_char(date_purchase, 'YYYY-MM') as xact_month, cost_price as amount from item
  union all
  select to_char(payment_date, 'YYYY-MM'), payment * -1 from payment
)
select distinct xact_month as month, 
       sum(amount) over (order by xact_month) as remaining_amount
  from ledger;

Working fiddle.

user_138083190

This is it:

select distinct date_trunc('month',m.date_1),m.num_1 from (select to_date(b."payment date",'DD-MM-YYYY') as date_1,
sum(a."cost_price"+coalesce((select sum("cost_price") from item_table c where 
                    date_trunc('month',to_date(a."date_purchase",'DD-MM-YYYY'))>date_trunc('month',to_date(c."date_purchase",'DD-MM-YYYY'))
                   ),0)-(coalesce((select sum("payment") from payment_table c where 
                    date_trunc('month',to_date(a."date_purchase",'DD-MM-YYYY'))>=date_trunc('month',to_date(c."payment date",'DD-MM-YYYY'))
                   ),0))) as num_1 from  item_table a,payment_table b
where date_trunc('month',to_date(a."date_purchase",'DD-MM-YYYY'))
=date_trunc('month',to_date(b."payment date",'DD-MM-YYYY'))
group by 1 order by 1)m;

please check at http://sqlfiddle.com/#!17/428874/37

possibly give it a green tick and an upvote..

user_62856000
Related