tsql query to give running balance

rajanisqldev-42 206 Reputation points
2022-11-24T14:13:50.123+00:00

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

263956-image.png

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

2 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-11-24T18:10:14.943+00:00
    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)  
    
    0 comments No comments

  2. rajanisqldev-42 206 Reputation points
    2022-11-25T09: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, '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

    0 comments No comments