January 11, 2013 at 9:02 am
Hi All,
I have two tables (Invoices and Payments) . The Invoices holds the amount at a Product Level whereas the Payments are done at an Invoice level. The issue is when an Invoice is part paid and we need to calculate the outstanding amount at Invoice and Product level.
The challenge is to allocate the payments on FIFO using the seq no on Invoice.
E.g
INVOICE PAYMENT
Invoice Seq No Product Amount InvoiceReceiptsAmount
I11P110I1R120
I12P215I2R240
I13P325
I21P115
I22P235
Here I1 is raised for P1 for 10 and the payment for it is 20 . Hence we fully allocate that invoice and the balance of 10 will be settled against the next product P2 with 15-(20-10) = 5 . Invoice I1 for P3 will not be allocated any amount as P2 still have an outstanding of 5.
OUTPUT
Invoice Seq No Product Amount Outstanding
I11P1100
I12P21510
I13P32525
I21P1150
I22P23510
I know we have a solution to this in a hard cursor way , but just exploring the possibility outside it .
Thanks
Sridhar
January 11, 2013 at 9:32 am
to get a meaningful answer, you need to provide the CREATE TABLE definitions for Invoices and Payments, and the INSERT INTO statements for your data;
i see that you pasted some representative data, but if you go to the trouble of providing readily consumable DDL and data, we can offer a working, testable solution.
Lowell
January 11, 2013 at 9:55 am
--Sorry , here it is :
Create Table Invoice (InvoiceId nvarchar(10), SeqNo smallint, Product nvarchar(10), Amount int)
Go
Insert into Invoice
Values ('I1',1,'P1',10),('I1',2,'P2',15),('I1',3,'P3',25),('I2',1,'P1',15),('I2',2,'P1',35)
Create Table Payment (InvoiceId nvarchar(10), ReceiptId nvarchar(10), Amount int)
Go
Insert into Invoice
Values ('I1','R1',20),('I2','R2',40)
January 11, 2013 at 1:04 pm
Sridhar, maybe something like this will work for you. I think the desired output you posted does not match the data you provided, because it looks like, according to your data, I1-P2 should have balance due of 5, not 10, unless I am completely misunderstanding your question. One caveat, if you need to do this to a large data set, the performance is going to be pretty bad. You would probably be better off using the quirky update method, but I've not tested, so that is just speculation. I don't know the link off hand, but if you search this site for "running totals", you should find Jeff Moden's great article about doing just this sort of thing.
WITH Data AS
(
SELECT
i.InvoiceId,
i.SeqNo,
i.Product,
iAmount = i.Amount,
p.ReceiptId,
Paid = p.Amount,
ROWNUM = ROW_NUMBER() OVER (PARTITION BY i.InvoiceId, p.ReceiptId ORDER BY i.InvoiceId, i.SeqNo)
FROM Invoice i INNER JOIN Payment p
ON i.InvoiceId = p.InvoiceId
)
,rCTE AS (
SELECT
InvoiceId,
SeqNo,
Product,
iAmount,
ReceiptId,
Paid,
ROWNUM,
RemainingDue = CASE WHEN Paid >= iAmount
THEN 0
ELSE iAmount - Paid
END,
CreditBalance = CASE WHEN Paid > iAmount
THEN Paid - iAmount
ELSE 0
END
FROM Data
WHERE ROWNUM = 1
UNION ALL
SELECT
d.InvoiceId,
d.SeqNo,
d.Product,
d.iAmount,
d.ReceiptId,
Paid = r.CreditBalance,
d.ROWNUM,
RemainingDue = CASE WHEN d.iAmount > r.CreditBalance
THEN d.iAmount - r.CreditBalance
ELSE NULL
END,
CreditBalance = CASE WHEN r.CreditBalance > d.iAmount
THEN r.CreditBalance - d.iAmount
ELSE 0
END
FROM Data d INNER JOIN rCTE r
ON d.InvoiceId = r.InvoiceId
AND d.ROWNUM = r.ROWNUM + 1
)
SELECT
InvoiceId,
SeqNo,
Product,
iAmount,
ReceiptId,
Paid,
RemainingDue,
CreditBalance
FROM rCTE
ORDER BY InvoiceId, SeqNo
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 11, 2013 at 4:17 pm
I'm pretty much amazed that people continue to reinvent the wheel. Buy a copy of something like QuickBooks or other simple accounting software and be done with it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2013 at 4:14 am
Oh, I heartily agree with you Jeff...but as long as they keep askin'.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 18, 2013 at 9:35 am
Guys, I too agreee that re-inventing the wheel is never a good option but then consider this where there are millions of historic transactions and the only way to cut down the size is by migrating open /part paid invoices and that too with this caveat that we need to use FIFO to allocate the invoices.. Long story short .. as much a pain it is, cant see a way around to convince the business to buy a new software to deal with it .
Now on the code , thanks Greg for this . However I have one question as to at any point , if i need to know which invoice has an credit balance then is there any way in the CTE that we can mark it ?
And yes as much as it works pretty well , i need to work out ways to optimize it to reduce the set to minimum.
thanks
Sridhar
January 18, 2013 at 11:32 am
sridhar_kola
if i need to know which invoice has an credit balance then is there any way in the CTE that we can mark it ?
Do you really have customers who pay more than is on the invoice? Or are you wanting to see invoices that have not been completely paid yet? My humble opinion is that would probably be better done in it's own query, as your rCTE already has enough to do. Something like this may work for you.
SELECT
i.InvoiceId,
Owes = SUM(i.Amount),
Paid = COALESCE(p.Paid,0)
FROM Invoice i LEFT OUTER JOIN
(
SELECT
InvoiceId,
SUM(Amount) AS Paid
FROM Payment
GROUP BY InvoiceId
) p
ON i.InvoiceId = p.InvoiceId
GROUP BY i.InvoiceId, p.Paid
HAVING SUM(i.Amount) - COALESCE(p.Paid,0) > 0
Also, with respect to performance, if you are talking millions of rows, the CTE is going to be like a cold turtle swimming in frozen molassas in Alaska in the middle of January. You could probably tweak the indexes to maximize performance, but I'm afraid I can't be of much help to you there. Hopefully someone with better performance tuning skills will reply.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply