Distribute Amounts in sql

rajanisqldev-42 206 Reputation points


I have a sql table with InvoiceNo, TransDate, InvoiceAmount, PaymentAmount, RetailInvoiceProductAmount, NonRetailInvoiceProductAmount

PaymentAmount is not split by Retail and NonRetail products

But, I need to get how much amount received by Retail and NonRetail products

Note: Every first payments by InvoiceNo is allocated to NonRetailProductAmount and the rest will be RetailProductAmount.

If the customer makes 4 payments for the invoice amount of £100(Retail: £80, NonRetail: £20)

Payment1: on 2022-04-01: £25
Payment2: on 2022-04-12 £35
Payment3: on 2022-04-20 £25
Payment4: on 2022-05-01 £15

I need to get final sql query as

InvoiceNo TransDate Total_InvoiceAmount, PaymentAmount, Retail_Payment (PaymentAmount-NonRetailProductAmount) --This needs to be like running values/calculation

In the above example, At first payment transaction, Retail_Payment value should be £5

I am not getting correct sql query to achieve this.

Can anyone put some light on this


A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points


    Welcome to Microsoft T-SQL Q&A Forum!

    Please check this:

    Create table #test  
    	InvoiceNo nvarchar(20),  
    	InvoiceAmount money,  
    	RetailnvoiceProductAmount money,  
    	NoRetailnvoiceProductAmount money,  
    	PaymentDate datetime,  
    	PaymentAmount money  
    Insert into #test values  
    With cte as  
    	select  *,sum(PaymentAmount)OVER(PARTITION BY InvoiceNo  
                                 ORDER BY PaymentDate  
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -NoRetailnvoiceProductAmount as column1  
    	from #test  
    ),cte1 AS  
    Select *,LAG(column1,1,-1)OVER(PARTITION BY InvoiceNo  
                                 ORDER BY PaymentDate) AS Column2  
    From cte  
    select InvoiceNo,InvoiceAmount,RetailnvoiceProductAmount,NoRetailnvoiceProductAmount,PaymentDate, PaymentAmount ,case when column1 <0 and Column2<0 then 0   
    when column1>0 and column2<0 then column1   
    else PaymentAmount   
    end  as RetailProductme_Amount  
    from cte1  


    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. rajanisqldev-42 206 Reputation points

    Hi @Olaf Helper

    Thanks. The table structure is simple


    Cells in green is the one I am struck


  2. Erland Sommarskog 106.6K Reputation points

    You can compute the accumulated payment amount as

    SELECT ... , SUM(PaymentAmount) OVER(PARTITION BY InvoiceNo 
                                 ORDER BY PaymentDate  
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalPaid

    and then you can take it from there.

    If you want a full query, you need to post CREATE TABLE for the table and the data as INSERT statements. We cannot copy from images.

    0 comments No comments

  3. rajanisqldev-42 206 Reputation points

    Hi @Erland Sommarskog

    Thanks for the reply.

    ('INV-002528', 100.00, 80.00, 20.00, '2022-04-01', 25.00),
    ('INV-002528', 100.00, 80.00, 20.00, '2022-04-12', 35.00),
    ('INV-002528', 100.00, 80.00, 20.00, '2022-04-26', 25.00),
    ('INV-002528', 100.00, 80.00, 20.00, '2022-05-01', 15.00)
    ) tab(InvoiceNo, InvoiceAmount, RetailInvoiceProductAmount, NonRetailInvoiceProductAmount, PaymentDate, PaymentAmount)

    Will this be sufficient?


    0 comments No comments