question

Kumar-6106 avatar image
0 Votes"
Kumar-6106 asked Kumar-6106 answered

Take PreviousDay balance and ADD\Substract to Calculate runningBalance

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'
137602-image.png



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.

sql-server-transact-sql
image.png (24.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @Kumar-6106,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Kumar-6106 avatar image
0 Votes"
Kumar-6106 answered

Thank you Melissa

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.