Dave Roger
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.