Evaluating 2 Fields and Returning the Larger

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

Evaluating 2 Fields and Returning the Larger

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-22 : 14:01:46

I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.

SELECT
COALESCE(CEOAnnualBonus, CEOBonus)
AS CEOBonusCombined
FROM tbenchmarktemp
WHERE Ticker='F'

Thanks for any help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-22 : 14:18:10

SELECT CEOBonusCombined = CASE WHEN CEOAnnualBonus > CEOBonus THEN CEOAnnualBonus ELSE CEOBonus END
FROM tbenchmarktemp
WHERE Ticker='F'

Tara Kizer
http://weblogs.sqlteam.com/tarad/

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-22 : 14:46:11

That's great. Thanks a lot.

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-05-22 : 14:48:45

Please try this query, hope it will work...

SELECT CASE WHEN ISNULL(CEOAnnualBonus,0) > ISNULL(CEOBonus,0) THEN CEOAnnualBonus
WHEN ISNULL(CEOAnnualBonus,0) < ISNULL(CEOBonus,0) THEN CEOBonus
WHEN ISNULL(CEOAnnualBonus,0) = ISNULL(CEOBonus,0) THEN CEOAnnualBonus
ELSE 0
END
AS CEOBonusCombined
FROM tbenchmarktemp
WHERE Ticker='F'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources