Solved

Invoice allocation without using cursor

Posted on 2011-03-24
3
443 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 250 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 250 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now