Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

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
0
fhcdaver
Asked:
fhcdaver
2 Solutions
 
Aaron ShiloChief Database ArchitectCommented:
hi

load the rows into a #temptable
and the use a while loop to review/maipulate every row at a time.

you can use the row_number() function while loading the rown into a #temptable to number them and move from row to in the #temptable.

read more here :http://www.sqlservercentral.com/articles/Advanced+Querying/replacingcursorsandwhileloops/1956/
0
 
davehilditchCommented:
if you use a temptable and a while loop that's just a cursor by a different name - in fact, just a manual cursor.

I can't follow your data unfortunately - are you sure the expected amounts are correct as you've written them?

Basically, you want to write two update statements inside a transaction.

Can you explain what your join columns are between u_inv and u_pay?  Also what relation does alloc_amount have?

You need the following:

1.  a query to sum the total paid by the payees (a group by query)
2. an update statement to update the not-yet-fully-paid invoices (date_full_paid is null) based on the group by query
3. an update statement to update the u_pay table i think to update the alloc_amount - not sure what this column is tho
0
 
fhcdaverpresidentAuthor Commented:
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.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now