Formating selected value according to anothre col

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

Formating selected value according to anothre col

Author

Topic

moooris
Starting Member

11 Posts

Posted - 2009-05-13 : 08:12:30

I have this table:

Id CardID Amount ToCard TimeStamp
----------- ----------- --------------------- ------ -----------------------
1 1 20.00 0 2009-03-18 09:07:00
2 1 15.00 1 2009-03-18 09:37:00
3 1 150.00 1 2009-03-18 10:49:00
4 2 200.00 1 2009-03-18 14:14:00

Im interested to know how i can select the Amount column as negative and positive according to the ToCard column bit.

So that the first row would be a negative value.

Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-13 : 08:15:04

SELECT CASE WHEN ToCard = 1 THEN Amount*-1 ELSE Amount END
FROM yourTable

Jim

moooris
Starting Member

11 Posts

Posted - 2009-05-13 : 08:23:29

quote:
Originally posted by jimf

SELECT CASE WHEN ToCard = 1 THEN Amount*-1 ELSE Amount END
FROM yourTable

Jim

Perfect!
Thank you so much.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-13 : 09:04:26

You're Welcome!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-13 : 09:39:32

Just curious - why would you store amount as a positive but depend on another field to determine its "real" value (positive or negative)? Wouldn't it be easier (wiser?) to store the true value in the database?

Also, one small typo on the solution provided by Jim:

SELECT CASE WHEN ToCard = 0 THEN Amount*-1 ELSE Amount END
FROM yourTable

Terry

-- Procrastinate now!

moooris
Starting Member

11 Posts

Posted - 2009-05-14 : 01:00:00

quote:
Originally posted by tosscrosby

Just curious - why would you store amount as a positive but depend on another field to determine its "real" value (positive or negative)? Wouldn't it be easier (wiser?) to store the true value in the database?

Also, one small typo on the solution provided by Jim:

SELECT CASE WHEN ToCard = 0 THEN Amount*-1 ELSE Amount END
FROM yourTable

Terry

-- Procrastinate now!

True enough, i figured later i could just store it in the natural way. but anyway i have learned to use the CASE WHEN. so i have no regrets :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources