0

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.

Code On
  • 213
  • 2
  • 5
  • 13
  • Please clarify the roles of `deduct_id` and `created_time`, show what you tried, and disclose your version of Postgres. – Erwin Brandstetter Aug 05 '20 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 Aug 06 '20 at 02:15

1 Answers1

2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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 – Code On Aug 06 '20 at 02:14