How to calculate balance in accounting using postgres with join table

Question

Ive got an issue same with this calculate balance in postgres but with join table.

I have table A with

ID amount deduct_id created_time 1 100.00 1 2020-01-01 15:30:20 2 10.00 1 2020-01-01 15:32:20 3 30.00 1 2020-01-01 15:43:20 4 5.00 1 2020-02-02 08:30:20 5 10.00 2 2020-02-02 23:30:20 6 20.00 2 2020-02-03 10:30:20

and table B with

deduct_id amount created_time 1 100.00 2020-02-02 10:00:20 2 15.00 2020-02-03 10:00:20

Now I need a query which gives me the following result:

ID amount deduct Balance created_time 1 100.00 0.00 100.00 2020-01-01 15:30:20 2 10.00 0.00 110.00 2020-01-01 15:32:20 3 30.00 0.00 140.00 2020-01-01 15:43:20 4 5.00 0.00 145.00 2020-02-02 08:30:20 null 0.00 100.00 45.00 2020-02-02 10:00:20 5 10.00 0.00 55.00 2020-02-02 23:30:20 null 0.00 15.00 40.00 2020-02-03 10:00:20 6 20.00 0.00 60.00 2020-02-03 10:30:20

i am using postgres 9.6

deduct_id is to indicate if a data is part of the deduction on that date.

created_time is to indicate timeline.

[Updated] and how to achieve filter by month?

ID amount deduct Balance created_time 1 100.00 0.00 100.00 2020-01-01 15:30:20 2 10.00 0.00 110.00 2020-01-01 15:32:20 3 30.00 0.00 140.00 2020-01-01 15:43:20 ID amount deduct Balance created_time 4 5.00 0.00 145.00 2020-02-02 08:30:20 null 0.00 100.00 45.00 2020-02-02 10:00:20 5 10.00 0.00 55.00 2020-02-02 23:30:20 null 0.00 15.00 40.00 2020-02-03 10:00:20 6 20.00 0.00 60.00 2020-02-03 10:30:20

I know this is bad design of table, but is it possible to achieve that kind of result? how would this be done?

thanks in advance, any help really appreciated.

Please clarify the roles of deduct_id and created_time, show what you tried, and disclose your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 5, 2020 at 22:07
updated @ErwinBrandstetter I want to use created_time to filter with WHERE condtion. so I can get balance on spesific range of month, with starting balance is last balance on the previous month — Code On
– Code On, Commented Aug 6, 2020 at 2:15

GMB · Accepted Answer · 2020-08-05 21:46:27Z

I think that's union all and a window sum():

select id, amount, deduct, sum(amount - deduct) over(order by created_time) balance, created_time from ( select id, amount, 0 as deduct, created_time from tablea union all select null, 0 as amount, amount as deduct, created_time from tableb ) t

I am unclear on what column deduct_id is supposed to be used for. From the results of the query, it looks like you don't want to use it to define partitions, as opposed to what I would have thought - so I just removed if from the query.

hi, thanks your answer help me. but if you dont mind, do you how to use WHERE created_time on the query? so I can filter by date, with starting amount is last balance on the previous month? @GMB

Collectives™ on Stack Overflow

How to calculate balance in accounting using postgres with join table

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related