locked
Adding minus sign to transaction amount if debit type RRS feed

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

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2015 7:01 AM