Distribute Amounts in sql

rajanisqldev-42 206 Reputation points
2022-05-04T13:37:49.427+00:00

Hi

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

Regards

Transact-SQL
Transact-SQL
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
    2022-05-05T09:17:24.043+00:00

    Hi,@rajanisqldev-42

    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  
    ('INV-002528',100,80,20,'2022-04-01',25),  
    ('INV-002528',100,80,20,'2022-04-12',35),  
    ('INV-002528',100,80,20,'2022-04-26',25),  
    ('INV-002528',100,80,20,'2022-05-01',15)  
    ;  
    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  
    

    199088-image.png

    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
    2022-05-04T17:32:06.173+00:00

    Hi @Olaf Helper

    Thanks. The table structure is simple

    198888-image.png

    Cells in green is the one I am struck

    Thanks


  2. Erland Sommarskog 106.6K Reputation points
    2022-05-04T21:58:52.53+00:00

    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
    2022-05-05T09:22:58.663+00:00

    Hi @Erland Sommarskog

    Thanks for the reply.

    SELECT * FROM(VALUES
    ('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?

    Thanks

    0 comments No comments