TSQL for Financial Settlements

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • --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)

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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

  • 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