Click here to Skip to main content
15,880,725 members
Search within:


How to do both addition and subtraction in running total based on a condition i.e., col value
Please Sign up or sign in to vote.
0.00/5 (No votes)
My requirement is very simple: I need to print out a monthly bank statement from a table using a stored procedure.

Everything seems fine, the problem is my requirement requires me to do a running total i.e., both addition and subtraction based on the transaction type.

There are three types of transactions which are CD Cash Deposit addition should happen to the existing total. CHQ cheque deposit is also the same as CD addition. But when CW cash withdrawal then we have to subtract the CW amount from the existing Transaction amount as the Running total sample table running total.

RNO DOT TXN_TYPE CHQ_NO TXN_AMOUNT RUNNING_TOTAL

1 28/03/2020 CQD 50 84790 84790
2 1/04/2020 CW null 59415 25375
3 25/10/2020 CQD 12 191785 217160
4 12/02/2021 CD null 125678 342838
5 23/09/2021 CW 10 15000 327838
In the table, we can see 1st row has TXN_Type CQD so TXN_Amount remains the same in the new col Running _total. whereas in the next row we have CW which means the subtraction of the TXN_Amount from the running total i.e, 84790 - 59415 = 25375 should be displayed in the running_total and then the 3rd row has CQD now we have to add new running_total amount to TXN_Amount i.e., 25375 + 191785 = 217160.

So, based on the TXN_TYPE we have to either add or subtract and print the running total. I'm aware of using running total by using sum(Txn_Amount) over (order by DOT) this works by adding the running total but when TXN_TYPE is CW we have to subtract.

Can someone help me do this?

I'm doing this in a stored procedure by creating a temp table and to that temp table, I have to add a new col as running col and do both addition and subtraction based on the TXN_TYPE as running total. I iterate the table for count condition in the while loop and print all the returned values from the select query. The adding works fine but subtraction logic is what troubles me. Can anyone help me write logic on how to subtract in running total for bank statements?

What I have tried:

select
ROW_NUMBER() over (order by DOT asc) as RNO,
DOT,
TXN_TYPE,
CHQ_NO,
TXN_AMOUNT,
case when TXN_TYPE = 'CD' then sum(TXN_AMOUNT) over (order by DOT)
when TXN_TYPE = 'CQD' then sum(TXN_AMOUNT) over (order by DOT)
when TXN_TYPE = 'CW' then -(TXN_AMOUNT - lag(TXN_AMOUNT) over
(order by DOT)) end
as running_total
into #TxnData from
TransactionMaster where datediff(YY,dot,getdate()) <= 2 and acid = 12 group by DOT, TXN_TYPE, CHQ_NO, TXN_AMOUNT
Posted
Updated 18-Jul-22 5:43am
Add a Solution

1 solution

If this was me I would do something like this ... this is my test data
SQL
declare @TransactionMaster table (DOT date, TXN_TYPE varchar(3), CHQ_NO int null, TXN_AMOUNT bigint)
insert into @TransactionMaster (DOT, TXN_TYPE, CHQ_NO, TXN_AMOUNT) values
('2020-03-28', 'CQD', 50, 84790)
,('2020-04-01',  'CW', null, 59415)
,('2020-10-25', 'CQD', 12, 191785)
,('2021-02-12', 'CD', null, 125678)
,('2021-09-23', 'CW', 10, 15000);
I would use a CTE (or a temporary table, or a sub-query) to "pre-prepare" my data by multiplying by (-1) to make the CW values negative. Then I can use a very simple SUM ... OVER to calculate the running total e.g.
SQL
;with preprepare as 
(
	select
	ROW_NUMBER() over (order by DOT asc) as RNO
	,DOT
	,TXN_TYPE
	,CHQ_NO
	,CASE WHEN TXN_TYPE = 'CW' THEN (-1) * TXN_AMOUNT ELSE TXN_AMOUNT END AS TXN_AMOUNT
	from @TransactionMaster where datediff(YY,dot,getdate()) <= 2 
)
select 
	RNO
	,DOT
	,TXN_TYPE
	,CHQ_NO
	,TXN_AMOUNT
	,SUM(TXN_AMOUNT) OVER (ORDER BY RNO) as RUNNING_TOTAL
from preprepare;
Results: 
RNO	DOT			TXN_TYPE	CHQ_NO	TXN_AMOUNT	RUNNING_TOTAL
1	2020-03-28	CQD			50		84790		84790
2	2020-04-01	CW			NULL	-59415		25375
3	2020-10-25	CQD			12		191785		217160
4	2021-02-12	CD			NULL	125678		342838
5	2021-09-23	CW			10		-15000		327838
 
Share this answer
 
Posted
Comments
CHill60 19-Jul-22 4:55am    
Troubleshooting:
1. You have NOT used the logic I supplied in that code - only the part that turns CD values negative. You need all of the query.
2. Get rid of the loop - SQL is a set-based language and there are very very few times you need a loop.
3. Get rid of where RNO = @x - that, and your loop is what is breaking your results
4. Use 
select 
	RNO
	,DOT
	,TXN_TYPE
	,CHQ_NO
	,TXN_AMOUNT
	,SUM(TXN_AMOUNT) OVER (ORDER BY RNO) as RUNNING_TOTAL
from #TxnData;
without a loop
Shivam Chinna 2022 19-Jul-22 6:57am    
Why I'm using a while loop is to print each row value by storing each row value into variables and then printing them in a report format. without the while loop how can I print the resultset from the temp table. You can see I'm iterating while loop until the @count times i.e., no of rows fetched and each row I'm storing and printing for output.
Shivam Chinna 2022 19-Jul-22 7:50am    
No worries CHill60 I got it. Thanks for your help I'm able to get my desired output now with your idea. Thanks a lot, you made my day.
CHill60 19-Jul-22 10:14am    
Many people fall into the loop trap! Glad you now have it sorted. This article (it's mine) might help too Processing Loops in SQL Server[^]
Shivam Chinna 2022 18-Jul-22 13:09pm    
_This is the output I'm getting __________________________________________________________________________________
SL.No DOT TXN_TYPE Cheque_No Amount RunningBalance
_______________________________________________________________________
1 Nov 13, 2021 cqd 50 84790.00 84790.00
2 Nov 28, 2021 cw 28 -59415.00 -59415.00
3 Jun 22, 2022 cqd 12 191785.00 191785.00
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Subtracting the deleted value from the total value
Running total of running total
Addition of values of a column in a table as per condition
Find immediate parent based on condition
Dropdown autoselect based on a condition
Addition Condition In Store Procedure Query.
Enabling/disabling grid row editing based on condition
How to calculate the average of a set of random numbers including additional conditions in C?
Transfering items from a listbox to another with some conditions
CELLS value with 2 condition C#

Advertise
Privacy
Cookies
Terms of Use
Last Updated 18 Jul 2022
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900