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, '20220101', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220201', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220301', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220401', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220501', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220601', 15.00)
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)
tsql query to give running balance
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, '20220101', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220201', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220301', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220401', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220501', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220601', 15.00)
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)
Expected output
2 answers
Sort by: Most helpful

Jingyang Li 5,891 Reputation points
20221124T18:10:14.943+00:00 
rajanisqldev42 206 Reputation points
20221125T09:53:33.14+00:00 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, '20220101', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220201', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220301', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220401', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220401', 60.00),
(1234, 'INV#123', 90.00, 30.00, '20220501', 15.00),
(1234, 'INV#123', 90.00, 30.00, '20220601', 15.00)
)tab (CustNo, InvNo, InvAmt, Charity_Amount, PaymentDate, Payment_Amount)Please try this
How to fix?
Regards