How to multiply value in a field by -1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to multiply value in a field by -1

Author

Topic

iown714
Starting Member

1 Post

Posted - 2007-11-01 : 00:33:42

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!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 00:38:16

[code]select City, PurchaseDescription, Cost,
Cost2 = case when PurchaseDescription = 'USB' then -Cost else Cost End
from yourtable[/code]

KH
[spoiler]Time is always against us[/spoiler]

aarumugavel
Starting Member

8 Posts

Posted - 2007-11-01 : 01:12:11

select city,purchasedescription,cost1, case when purchasedescription = 'USB' then
cost1 * -1 else cost1 end cost 2 from main table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 01:21:00

if the alias name or table name contains space, enclose it in [ ].

select city,purchasedescription,cost1, case when purchasedescription = 'USB' then
cost1 * -1 else cost1 end [cost 2] from [main table]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 02:14:15

quote:
Originally posted by khtan

if the alias name or table name contains space, enclose it in [ ].

select city,purchasedescription,cost1, case when purchasedescription = 'USB' then
cost1 * -1 else cost1 end [cost 2] from [main table]

KH
[spoiler]Time is always against us[/spoiler]

Or better dont use space instead use underscore

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-01 : 02:36:03

how to take another case for the same query

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 02:49:46

quote:
Originally posted by sunsanvin

how to take another case for the same query

Vinod
Even you learn 1%, Learn it with 100% confidence.

What case are you referring to ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 03:13:06

quote:
Originally posted by sunsanvin

how to take another case for the same query

Vinod
Even you learn 1%, Learn it with 100% confidence.

Select
cas when ........... end as col1,
cas when ........... end as col2,
cas when ........... end as col3
from table

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-01 : 06:06:34

this will satisfy you
make sure after keyword CASE put the column name on which case to be applied
here underlined

select city,purchasedescription PD,cost,cost1= CASE PD when 'USB' then Cost*-1 else cost end
from [Tablename]

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 06:11:26

quote:
Originally posted by arorarahul.0688

this will satisfy you
make sure after keyword CASE put the column name on which case to be applied
here underlined

select city,purchasedescription PD,cost,cost1= CASE PD when 'USB' then Cost*-1 else cost end
from [Tablename]

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

a negative demonstration ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources