tsql query to give running balance

Question

Hi

I need to find running balance for the charity amount at each payment transaction level

SELECT * FROM (VALUES
(1234, 'INV#123', 90.00, 30.00, '2022-01-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-02-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-03-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-04-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-05-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-06-01', 15.00)
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)

Expected output

Answer 1

SELECT *,  
Case when Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate  ) >=0 then Payment_Amount else 0 end ,  
Case when Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate  )>=0 then  
Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate  )  
else 0 end Charity_Balance   
FROM (VALUES  
(1234, 'INV#123', 90.00, 30.00, '2022-01-01', 15.00),  
(1234, 'INV#123', 90.00, 30.00, '2022-02-01', 15.00),  
(1234, 'INV#123', 90.00, 30.00, '2022-03-01', 15.00),  
(1234, 'INV#123', 90.00, 30.00, '2022-04-01', 15.00),  
(1234, 'INV#123', 90.00, 30.00, '2022-05-01', 15.00),  
(1234, 'INV#123', 90.00, 30.00, '2022-06-01', 15.00)  
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)  

Answer 2

Hi @Jingyang Li

Thanks for the reply. You query works fine if the payments are in sequence.

There are possibilities that there will be refunds and repayments

SELECT *,
Case when Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate ) >=0 then Payment_Amount else 0 end ,
Case when Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate )>=0 then
Charity_Amount - sum(Payment_Amount) over(partition by CustNo, InvNo order by PaymentDate )
else 0 end Charity_Balance
FROM (VALUES
(1234, 'INV#123', 90.00, 30.00, '2022-01-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-02-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-03-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-04-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-04-01', -60.00),
(1234, 'INV#123', 90.00, 30.00, '2022-05-01', 15.00),
(1234, 'INV#123', 90.00, 30.00, '2022-06-01', 15.00)
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)

Please try this

How to fix?

Regards