CODEWITHSUNDEEP
sqlsql-serverselectnegative-number
the606
the606

Reputation: 51

Update values to negative based on another column

I have a table that contains sales invoices and credit notes, both are held in the table as positive values.

I want to select the data and in the select statement change the credit note vales to be negative based on another column.

Any ideas?

Updated with added information

Key          Description            Type          Amount
--------------------------------------------------------
1            Invoice for 345         1             200
2            Invoice for 346         1             250
3            Invoice for 347         1             280
4            Credit  for 347         2             200
5            Invoice for 345         1             400
6            Credit note zz665       2             380

Desired result from select


Key          Description            Type          Amount
--------------------------------------------------------
1            Invoice for 345         1             200
2            Invoice for 346         1             250
3            Invoice for 347         1             280
4            Credit  for 347         2            -200
5            Invoice for 345         1             400
6            Credit note zz665       2            -380

thanks a lot, i have playing around with ABS but i cant figure out the exact usage to get my desired output.

Upvotes: 0

Views: 2157

Answers (3)

Guest
Guest

Reputation: 1

In case anyone has this question and is using DB2:

Select Key
      ,Description
      ,Type
      ,Amount
      ,Amount * (case when type = '1' then 1 else -1 end) as Amount_Accurate
 From  YourTable
  • as a side note, if you're in my situation where a column named charge_type says either Credit or Debit, then you could do the following:
Select Key
      ,Description
      ,Type
      ,Amount
      ,Amount * (case when charge_type = 'Charge' then 1 else -1 end) as Amount_Accurate
 From  YourTable

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Just another option is choose()

Example

Select [Key] 
      ,[Description]
      ,[Type]
      ,[Amount] = [Amount]*choose([type],1,-1)
 From  YourTable

Upvotes: 1

Jim L
Jim L

Reputation: 175

I am assuming that when type = 1, then positive and when type = 2 then negative. If the type could include more values you will have to amend the case statement because everything other than 1 will be negative. 

select
  key
, description
, type
, case
   when type = 1 then amount
  else
   amount * -1
  end as amount

Upvotes: 0

Related Questions