Adding minus sign to transaction amount if debit type

Question

User-34860367 posted

Hi all,

I have a query 

SELECT Transaction_No, Transaction_Type, Amount    
FROM TransactionTable

The above data result will be:

Transaction_No | Transaction_Type | Amount 
1, debit, 1000
2, credit, 6000
3, debit, 2000
4, credit, 34000
5, credit, 67000
6, credit, 4500
7, credit, 45000
8, credit, 79000
9, debit, 5400
10, debit, 200

...

I need another query to produce the following data

Transaction_No | Transaction_Type | Amount | AmountwithSign | Sum(AmountwithSign)
1, debit, 1000, -1000
2, credit, 6000, 6000
3, debit, 2000, -2000
4, credit, 34000, 34000
5, credit, 67000, 67000
6, credit, 4500, 4500
7, credit, 45000, 45000
8, credit, 79000, 79000
9, debit, 5400, -5400
10, debit, 200, -200

...

Basically, I have a Transaction table with above column field data and need to add another column on the fly based on transaction type, then 
calculate the Sum based on new added column. Thanks in advance.

Answer 1

User269602965 posted

CASE

  WHEN TRANSACTION_TYPE = 'debit' THEN AMOUNT * -1

  WHEN TRANSACTION_TYPE = 'credit' THEN AMOUNT

END AS AMOUNT_WITHSIGN

as far as SUM goes,

specify type of summing.

is this CUMULATIVE SUM row by row?

Answer 2

User-219423983 posted

Hi avt2k7,

calculate the Sum based on new added column

The following result is my understanding, if I’m wrong, please let me know.

1, debit, 1000, -1000,-1000
2, credit, 6000, 6000,5000
3, debit, 2000, -2000,3000
4, credit, 34000, 34000,37000

If the result above is what you want, you could refer to the following code, I have tested it on my client and it works.

select Transaction_No,Transaction_Type,Amount,
case
when  [Transaction_Type ]= 'debit' then [Amount ]*-1 else [Amount ] end as 'AmountwithSign'
into #temp
from dbo.TransactionTable


select t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign,sum(t2.AmountwithSign)

from #temp t1, #temp t2

where t1.Transaction_No >= t2.Transaction_No
group by t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign
Order by t1.Transaction_No

drop table #temp 

I hope it’s useful to you.

Best Regards,

Weibo Zhang