Monthly cumulative differences calculation in postgres

Question

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

Why is it not answering your question? What is wrong ? – WiatroBosy Dec 14 '21 at 14:45 — WiatroBosy, Dec 14 '21 at 14:45

score 0 · Answer 1 · answered Dec 14 '21 at 15:05

0

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...

answered Dec 14 '21 at 15:05

SQLpro

3,994
1
6
14

score 0 · Answer 2 · answered Dec 14 '21 at 15:41

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.

Nikhil S · Answer 3 · 2021-12-19T12:52:27.073

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..

Monthly cumulative differences calculation in postgres

3 Answers3