How to multiply value in a field by -1 under certain circumstances 1

[iown714]

Hello, I have a quick question. I'm currently using MS SQL 2005.

I have a main table which has many fields. I created a view#1 which imports all the rows from the main table but I only select the "City" field, "PurchaseDescription" field and a "Cost" field. Only the city name is unique. There are a total of 50 different city names, and each name can have more than one "PurchaseDescription" each with an associated "Cost". I would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to "USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the "PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as "Cost".


For example,

The view#1 will have the following rows & fields (I had to pad the field with dots just to make the output look viewable on this thread)

City..............PurchaseDescription............Cost
--------------------------------------------
LA.................desk................................4.5
LA.................USB.................................5.0
LA.................USB.................................6.0
SD................chair.................................4.0
SD................door.................................10.0

The view#2 should have the following rows and fields


City..............PurchaseDescription............Cost.......Cost2
---------------------------------------------------------
LA.................desk................................4.5....... 4.5
LA.................USB.................................5.0....... -5.0
LA.................USB.................................6.0....... -6.0
SD................chair.................................4.0....... 4.0
SD................door.................................10.0....... 10.0

I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need a final view that would look like view#2.

Could you please help if you can?

Thank you very much for your time and have a safe Halloween!

 

[bborissov]

SELECT City, 
       PurchaseDescription,
       Cost,
       Cost * CASE WHEN PurchaseDescription = 'USB'
                   THEN -1
                   ELSE 1 END AS Cost2
FROM ....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[iown714]

Thank you very much Borislav! It worked! I originally used the CASE as well but I was missing some key parts.

 

[ESquared]

You can make things line up using the [ignore][tt][/tt][/ignore] tags. So

[ignore][tt]this will |
line up |
perfectly |[/tt][/ignore]

yields:

[tt]this will |
line up |
perfectly |[/tt]

And since this raises another possible curiosity, you can display those tags without them "working" as so:

[ignore][ignore][tt]example text[/tt][/ignore][/ignore]

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]