I am trying to achieve distribution of payments over multiple invoice transactions as part of a batch process. The invoice and payment transactions are kept in the same table, that acts as a transactions register.
So for eg. we have in our transactions register table:
ID, USER , TYPE , AMOUNT
1 Mr. X Invoice 1000
2 Mr. X Invoice 2000
3 Mr. X Invoice 1000
4 Mr. X Payment -3000
5 Mr. X Payment -500
I am looking for a query that will take this total payment of 3500 for this user, start from the first invoice, distribute the payment to each invoice and dump it in a new table.
The end result for the new table would be as below.
ID User , TYPE , AMOUNT , AMOUNT_PAID
1 Mr. X Invoice 1000 1000
2 Mr. X Invoice 2000 2000
3 Mr. X Invoice 1000 500
I am trying to avoid using loops in PL/SQL blocks. Any comments are highly appreciated!
So this solution uses two analytic functions. In the inner query it uses an analytic SUM() to track a rolling total of invoice amounts. In the outer query it uses LAG() to get the previous sum of invoices when the total paid is insufficient.
select id
, username
, amount
, case when tot_amount >= rolling_pay
then amount
else
tot_amount - lag(rolling_pay) over (order by id)
end as amount_paid
from (
with inv as (select id
, username
, amount
from transactions
where type = 'Invoice' )
, pay as ( select username
, abs(sum(amount)) as tot_amount
from transactions
where type = 'Payment'
group by username )
select inv.id
, inv.username
, inv.amount
, pay.tot_amount
, sum(inv.amount) over (partition by inv.username order by inv.id) as rolling_pay
from inv join pay
on inv.username = pay.username
order by inv.username, inv.id
)
Note: I summed the payments, for when there is more than one per business key.
Here is the inevitable SQL Fiddle demonstrating that this produces the desired result.