Running Balance on Union Tables

Question

The code below computes the running outstanding balance for the tables ACCOUNT and PAYMENT:

select 
    b.payment_date,    
    a.account_no, a.accountname, a.loan_amount,
    b.amount, 
    ob = a.loan_amount - sum(b.amount) over (partition by b.account_no order by b.payment_date) 
from 
    account a 
inner join
    (select * from payment) b on a.account_no = b.account_no 
order by 
    a.account_no, b.payment_date

Payment:

    +--------------+------------+----------+
    | payment_date | account_no |  amount  |
    +--------------+------------+----------+
    | 2017-08-10   |  123456789 |   5000   |
    | 2017-08-15   |  987654321 |   3000   |
    | 2017-09-15   |  987654321 |   3000   |
    | 2017-10-11   |  123456789 |   4000   |
    | 2017-10-16   |  987654321 |   3500   |
    | 2017-11-10   |  123456789 |   3000   |
    | 2017-11-15   |  987654321 |   2500   |
    +--------------+------------+----------+

Account:

    +--------------+-------------+---------------+
    | account_no   | accountname |  loan_amount  |
    +--------------+-------------+---------------+
    |  123456789   |     John    |   15000       |
    |  987654321   |     Jane    |   20000       |
    +--------------+-------------+---------------+

QUERY RESULT:

    +--------------+------------+----------+----------------------+
    | payment_date | account_no |  amount  | outstanding_balance  |
    +--------------+------------+----------+----------------------+
    | 2017-08-10   |  123456789 |   5000   |        10000         |
    | 2017-10-11   |  123456789 |   4000   |         6000         |
    | 2017-11-10   |  123456789 |   3000   |         3000         |
    | 2017-08-15   |  987654321 |   3000   |        17000         |
    | 2017-09-15   |  987654321 |   3000   |        14000         |
    | 2017-10-16   |  987654321 |   3500   |        11500         |
    | 2017-11-15   |  987654321 |   2500   |         9000         |
    +--------------+------------+----------+----------------------+

I want to add Discount table in the computation. This table contains the same structure as the Payment table.

Discount

    +--------------+------------+----------+
    | payment_date | account_no |  amount  |
    +--------------+------------+----------+
    | 2017-08-10   |  123456789 |   100    |
    | 2017-08-15   |  987654321 |   100    |
    | 2017-09-15   |  987654321 |   100    |
    +--------------+------------+----------+

So I decided to add a UNION on my query below. But it returns incorrect. values. Please help

select 
    b.payment_date, 
    a.account_no, a.accountname, a.loan_amount,
    b.amount,
    ob = a.loan_amount - sum(b.amount) over (partition by b.account_no order by b.payment_date) 
from 
    account a 
inner join
    (select * from payment union select * from discount) b on a.account_no = b.account_no 
order by 
    a.account_no, b.payment_date

could you add a Rextester with table defintions and some test data? would be easier to reproduce & test for us — Esteban P., Aug 30 '17 at 10:19

TOUZENE Mohamed Wassim · Answer 1 · 2017-08-30T10:41:01.757

Use another INNER JOIN

select 
    b.payment_date, 
    a.account_no, 
    a.loan_amount,
    b.amount,
    ob = a.loan_amount - sum(b.amount) over (partition by b.account_no order by b.payment_date),
    d.amount 
from 
    account a 
inner join
    payment b on a.account_no = b.account_no 
inner join
    discount d on (d.account_no = b.account_no and d.payment_date = b.payment_date)
order by 
    a.account_no, b.payment_date

score 0 · Answer 2 · answered Aug 30 '17 at 15:31

the discount amount is not reflecting in your computation. ob = a.loan_amount - sum(b.amount) you might not necessarily need the union, since discount is a separate table, you can just inner join the table and use the discount amount to reduce the loan_amount ob = (a.loan_amount- discount_amount) - sum(b.amount)

Running Balance on Union Tables

2 Answers2