Answered by:
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.Wednesday, August 12, 2015 1:27 PM
Answers
-
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?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, August 12, 2015 2:03 PM -
User-219423983 posted
Hi avt2k7,
calculate the Sum based on new added columnThe 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,37000If 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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 13, 2015 7:01 AM
All replies
-
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?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, August 12, 2015 2:03 PM -
User-219423983 posted
Hi avt2k7,
calculate the Sum based on new added columnThe 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,37000If 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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, August 13, 2015 7:01 AM