October 31, 2007 at 10:40 pm
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!
November 1, 2007 at 12:36 am
Just use case statement in #view1 as
SELECT .....,
( CASE WHEN PurchaseDescription = 'USB' THEN -Cost ELSE Cost END ) AS Cost2
FROM .....
--Ramesh
November 2, 2007 at 12:43 am
Thanks Ramesh for your tip. It worked. Thanks again!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply