1

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!

Angad
  • 3,389
  • 3
  • 30
  • 40
  • 1
    Can there be more than one payment for a given user (or whatever your actual grouping key is)? – APC Jun 26 '14 at 19:50
  • Yes there can be more than 1 payment for a user. So we're trying to distribute the sum of payments to the individual invoices. If payment exceeds, last invoice gets overpaid by the overpayment value. I have made some edits to the post to represent multiple payments. – Angad Jun 26 '14 at 19:54

1 Answers1

4

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.

APC
  • 144,005
  • 19
  • 170
  • 281