Take PreviousDay balance and ADD\Substract to Calculate runningBalance

Question

Need help with query.

CREATE TABLE #Temp (AcctNo INT, AsOfDate DATE, PreviousDayBal NUMERIC(18,2), TransAmount NUMERIC(18,2), Credit_Debit CHAR(10))
INSERT INTO #Temp
SELECT 150, '2021-04-29', 100, 20, 'Credit' UNION ALL
SELECT 150, '2021-04-29', 100, 30, 'Debit' UNION ALL
SELECT 150, '2021-04-29', 100, 15, 'Debit' UNION ALL
SELECT 150, '2021-04-29', 100, 60, 'Credit'

Need to take PreviousDay Balance for each account and add\substract to the TransAmount. The order Credit_Debit DESC ( Credits first and Debits next)
First result is source data and second result is my expected result.

Answer 1

Hi @Kumar ,

Welcome to Microsoft Q&A!

Please refer to below:

select AcctNo,AsOfDate,PreviousDayBal,TransAmount,Credit_Debit  
, PreviousDayBal + sum(case when Credit_Debit='Debit' then TransAmount*-1 else TransAmount end) OVER (partition by AcctNo ORDER BY rn ROWS UNBOUNDED PRECEDING) RunningBalance  
from (  
select AcctNo,AsOfDate,PreviousDayBal,TransAmount  
,Credit_Debit,ROW_NUMBER() over (partition by AcctNo order by Credit_Debit) rn   
from #Temp) a   
order by rn  

Output:

AcctNo AsOfDate PreviousDayBal TransAmount Credit_Debit RunningBalance  
150 2021-04-29 100.00 20.00 Credit     120.00  
150 2021-04-29 100.00 60.00 Credit     180.00  
150 2021-04-29 100.00 30.00 Debit      150.00  
150 2021-04-29 100.00 15.00 Debit      135.00  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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

Thank you Melissa