select case issue - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

select case issue

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-26 : 13:28:42

Hi

I try to get a select case query working. I try to use a snippet like this..


						 CASE SUM(dbo.Rating.Rating) 
						 WHEN (dbo.Grade.GreenGrade * MAX(dbo.SurveyAnswerInfo.UserID)) 
                         THEN 'Green' 
						 WHEN SUM(dbo.Rating.Rating) BETWEEN (dbo.Grade.YellowGrade * MAX(dbo.SurveyAnswerInfo.UserID)) AND (dbo.Grade.GreenGrade * MAX(dbo.SurveyAnswerInfo.UserID))  
						 THEN 'Greenish' 
						 WHEN (dbo.Grade.YellowGrade * MAX(dbo.SurveyAnswerInfo.UserID)) 
                         THEN 'Yellow' 
						 WHEN (dbo.Grade.RedGrade * MAX(dbo.SurveyAnswerInfo.UserID)) THEN 'Red' 
						 END AS CompletedColor

But that does not work, but it should show what I try to accomplish, but why doesn't it work? It give me this error "Incorrect syntax near the keyword 'BETWEEN'."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 13:47:31

can you let us know what you're trying to achieve with some sample data? the CASE ..WHEN in current form doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-26 : 13:52:53

This is a stripped version...

DECLARE @SumOf int
SET @SumOf = 60

SELECT
CASE @SumOf
WHEN 60
THEN 'Green'
WHEN @SumOf BETWEEN 40 AND 60
THEN 'Greenish'
WHEN 40
THEN 'Yellow'
WHEN 20 THEN 'Red'
END AS CompletedColor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 14:13:23

you cant mix simple and composite case statements. it has to be:-


DECLARE @SumOf int
SET @SumOf = 60

SELECT 
CASE  
WHEN @SumOf = 60 
THEN 'Green' 
WHEN @SumOf BETWEEN 40 AND 60 
THEN 'Greenish' 
WHEN @SumOf = 40 
THEN 'Yellow' 
WHEN @SumOf = 20 
THEN 'Red' 
END AS CompletedColor

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-26 : 14:19:09

Ahh, thank you very much!

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-26 : 14:41:33

One follow up question.... if I would want to have a certain condition for one GenderID and onother one for another like this...

DECLARE @SumOf int
DECLARE @GenderID int
SET @SumOf = 90
SET @GenderID = 1

SELECT

CASE @GenderID
WHEN 1 THEN

CASE
WHEN @SumOf > 60
THEN 'Green'
WHEN @SumOf = 60
THEN 'Green'
WHEN @SumOf BETWEEN 40 AND 60
THEN 'Greenish'
WHEN @SumOf = 40
THEN 'Yellow'
WHEN @SumOf = 20
THEN 'Red'
END AS CompletedColor

ELSE

CASE
WHEN @SumOf > 50
THEN 'Green'
WHEN @SumOf = 50
THEN 'Green'
WHEN @SumOf BETWEEN 30 AND 50
THEN 'Greenish'
WHEN @SumOf = 30
THEN 'Yellow'
WHEN @SumOf = 10
THEN 'Red'
END AS CompletedColor

END

How would I do that, it does not work now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 14:58:02

[code]
DECLARE @SumOf int
DECLARE @GenderID int
SET @SumOf = 90
SET @GenderID = 1

SELECT

CASE @GenderID
WHEN 1 THEN

CASE
WHEN @SumOf > 60
THEN 'Green'
WHEN @SumOf = 60
THEN 'Green'
WHEN @SumOf BETWEEN 40 AND 60
THEN 'Greenish'
WHEN @SumOf = 40
THEN 'Yellow'
WHEN @SumOf = 20
THEN 'Red'
END
ELSE

CASE
WHEN @SumOf > 50
THEN 'Green'
WHEN @SumOf = 50
THEN 'Green'
WHEN @SumOf BETWEEN 30 AND 50
THEN 'Greenish'
WHEN @SumOf = 30
THEN 'Yellow'
WHEN @SumOf = 10
THEN 'Red'
END
END AS CompletedColor
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-26 : 15:11:09

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources