Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • 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
 
fhcdaverAuthor 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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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