Oracle SQL Query for distributing payments -

Oracle SQL Query for distributing payments -

i trying accomplish distribution of payments on multiple invoice transactions part of batch process. invoice , payment transactions kept in same table, acts transactions register.

so eg. 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 looking query take total payment of 3500 user, start first invoice, distribute payment each invoice , dump in new table.

the end result new table 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 trying avoid using loops in pl/sql blocks. comments highly appreciated!

so solution uses 2 analytic functions. in inner query uses analytic sum() track rolling total of invoice amounts. in outer query uses lag() previous sum of invoices when total paid insufficient.

select id , username , amount , case when tot_amount >= rolling_pay amount else tot_amount - lag(rolling_pay) on (order id) end amount_paid ( inv (select id , username , amount transactions type = 'invoice' ) , pay ( select username , abs(sum(amount)) tot_amount transactions type = 'payment' grouping username ) select inv.id , inv.username , inv.amount , pay.tot_amount , sum(inv.amount) on (partition inv.username order inv.id) rolling_pay inv bring together pay on inv.username = pay.username order inv.username, inv.id )

note: summed payments, when there more 1 per business key.

here inevitable sql fiddle demonstrating produces desired result.

sql oracle billing oracle-analytics