CODEWITHSUNDEEP
sqldatabasepostgresqlsumwindow-functions
Code On
Code On

Reputation: 233

How to calculate balance in accounting using postgres with join table

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.

Upvotes: 0

Views: 307

Answers (1)

GMB
GMB

Reputation: 222582

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.

Upvotes: 2

Related Questions