Distribute Amounts in sql

Question

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

Answer 1

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  

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.

Answer 2

Hi @Olaf Helper

Thanks. The table structure is simple

Cells in green is the one I am struck

Thanks

Answer 3

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.

Answer 4

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