CODEWITHSUNDEEP
sql
user2022883
user2022883

Reputation: 29

How do I change a field to a negative based on another field's value in SQL

With the below statement I would like amount to be negative if [Invoice_Type_Code] = 'c', and if is not then it is positive.

SELECT [Invoice_Amount]
FROM [Forefront].[dbo].[VN_GL_DISTRIBUTION_HEADER_MC]
WHERE [Vendor_Code] ='  UnitedEL' and
[Date_List1]  > '2011-12-31' and
[Date_List1] < '2012-02-01' and
[Company_Code] = 'tmg'

Upvotes: 2

Views: 1120

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Depending on your RDBMS, using CASE could work -- most RDBMS should support it:

SELECT CASE WHEN  Invoice_Type_Code = 'C' THEN -1 ELSE 1 END * Invoice_Amount
FROM [Forefront].[dbo].[VN_GL_DISTRIBUTION_HEADER_MC]
where [Vendor_Code] ='  UnitedEL' and
[Date_List1]  > '2011-12-31' and [Date_List1] < '2012-02-01' and
[Company_Code] = 'tmg'

This assumes all Invoice_Amounts are positive. If they aren't positive and you still need negative values, use the ABS function and alter your case statement slightly.

Here is the Fiddle.

Good luck.

Upvotes: 2

Related Questions