## 2 replies

Last post Aug 13, 2015 07:01 AM by Weibo Zhang

• avt2k7

Member

169 Points

414 Posts

### Adding minus sign to transaction amount if debit type

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, 10002, credit, 60003, debit, 20004, credit, 340005, credit, 670006, credit, 45007, credit, 450008, credit, 790009, debit, 540010, debit, 200...`
`I need another query to produce the following data`
`Transaction_No | Transaction_Type | Amount | AmountwithSign | Sum(AmountwithSign)1, debit, 1000, -10002, credit, 6000, 60003, debit, 2000, -20004, credit, 34000, 340005, credit, 67000, 670006, credit, 4500, 45007, credit, 45000, 450008, credit, 79000, 790009, debit, 5400, -540010, 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.`
• Lannie

Contributor

3412 Points

1329 Posts

### Re: Adding minus sign to transaction amount if debit type

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?

• Weibo Zhang

Star

7970 Points

1586 Posts

### Re: Adding minus sign to transaction amount if debit type

Aug 13, 2015 07:01 AM|Weibo Zhang|LINK

Hi avt2k7,

#### 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