Link to home
Start Free TrialLog in
Avatar of Dave Roger
Dave RogerFlag for United States of America

asked on

Invoice allocation without using cursor

Is it possible to allocate the payments made by eeID (payees) to invoices as below without using cursors?

I have tables u_inv and u_pay

SELECT * FROM [u_inv]
id  eeID      due_date       amt_due  amt_paid      date_full_paid
1      1      2010-01-01      100.00      20.00       2009-12-01
2      1      2010-01-01      150.00      0.00            NULL
3      1      2010-02-01      200.00      0.00            NULL

4      2      2010-01-01      100.00      100.00      NULL
5      2      2010-01-01      350.00      0.00            NULL
6      2      2010-02-01      200.00      0.00            NULL

SELECT * FROM [u_pay]
id  eeID      pay_date        pay_amt     alloc_amt
1      1      2010-01-01      90.00      10.00
2      1      2010-02-01      200.00      0.00
3      2      2010-01-01      100.00      0.00
4      2      2010-02-01      500.00      0.00



After allocation, the expected results;
SELECT * FROM [u_inv]
id  eeID      due_date     amt_due      amt_paid date_full_paid
1      1      2010-01-01      100.00      100.00      2010-01-01
2      1      2010-01-01      150.00      150.00      2010-02-01
3      1      2010-02-01      250.00      50.00      NULL
4      2      2010-01-01      100.00      100.00      2009-12-01
5      2      2010-01-01      350.00      350.00      2010-01-01
6      2      2010-02-01      200.00      200.00      2010-01-01

SELECT * FROM [u_pay]
id  eeID      pay_date    pay_amt      alloc_amt
1      1      2010-01-01      90.00      90.00
2      1      2010-02-01      200.00      200.00
3      2      2010-01-01      100.00      100.00
4      2      2010-02-01      500.00      450.00
 allocation.sql
ASKER CERTIFIED SOLUTION
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dave Roger

ASKER

Hi Dave,

Thanks for your response.

The join columns between u_inv and u_pay are -
u_inv.eeID = u_pay.eeID

u_pay.alloc_amount is the allocated payment amounts to invoices

if we have invoice amount of $100 and we paid $150 then 100 will have been allocated to invoice, we then know we still have $50 available in overage account for any future invoices.

u_inv.inv_amt = 100

u_pay.pay_amt = 150 and u_pay.alloc_amt = 100

The u_inv.due_date is also important, payments need to be allocated on fifo basis, old first.