Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Invoice allocation without using cursor

Posted on 2011-03-24
3
Medium Priority
?
520 Views
Last Modified: 2012-05-11
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
Comment
Question by:fhcdaver
3 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 1000 total points
ID: 35213024
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
 
LVL 4

Assisted Solution

by:davehilditch
davehilditch earned 1000 total points
ID: 35215248
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
 

Author Comment

by:fhcdaver
ID: 35216093
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question