Using the Case command - 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

General SQL Server Forums

New to SQL Server Programming

Using the Case command

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2012-02-23 : 15:15:31

Hi
Have a look at my SQL statement below and let me know if there is a simpler way to get the Age slab for a given intiger..

quote:
Also note that the ">" sign in the last when clause gives me the following error.

Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '>'.

declare @Age int
set @Age = 20


declare @AgeSlab nvarchar(10)

select @AgeSlab = case @Age
	when 0 then '0-1'
	when 1 then '0-1'
	when 2 then '2-4'
	when 3 then '2-4'
	when 4 then '2-4'
	when 5 then '5-9'
	when 6 then '5-9'
	when 7 then '5-9'
	when 8 then '5-9'
	when 9 then '5-9'
	when 10 then '10-19'
	when 11 then '10-19'
	when 12 then '10-19'
	when 13 then '10-19'
	when 14 then '10-19'
	when 15 then '10-19'
	when 16 then '10-19'
	when 17 then '10-19'
	when 18 then '10-19'
	when 19 then '10-19'
	when > 20 then '20+'

	
	else 'Unknown'
end

select @AgeSlab

Ewan Gilby

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-23 : 15:25:17

try this


declare @Age int
set @Age = 20

declare @AgeSlab nvarchar(10)

select @AgeSlab = 
case
	when @Age between 0 and 1 then '0-1'
	when @Age between 2 and 4 then '2-4'
	when @Age between 5 and 9 then '5-9'
	when @Age between 10 and 19 then '10-19'
	when @Age >= 20 then '20+'
	else 'Unknown'
end

select @AgeSlab

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 15:41:34

I would have created a AgeRange table for this with Min and Max range values and range description

then the case statement will reduce to a join to this table which will be like


SELECT other columns,
ar.RangeDesc
FROM yourtable t
INNER JOIN AgeRange ar
ON t.Age BETWEEN ar.Min AND ar.Max

this will have flexibility of redefining,adding or removing ranges as per you need and you dont have to mess within procedure/inline code to get CASE expression changed each time

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

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2012-02-24 : 06:59:50

quote:
Originally posted by visakh16

I would have created a AgeRange table for this with Min and Max range values and range description

then the case statement will reduce to a join to this table which will be like

SELECT other columns,
ar.RangeDesc
FROM yourtable t
INNER JOIN AgeRange ar
ON t.Age BETWEEN ar.Min AND ar.Max

this will have flexibility of redefining,adding or removing ranges as per you need and you dont have to mess within procedure/inline code to get CASE expression changed each time

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

Please provide me an example on how to create an AgeRange table.. Thanks

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2012-02-24 : 09:57:37

quote:

Originally posted by webfred

try this


declare @Age int
set @Age = 20

declare @AgeSlab nvarchar(10)

select @AgeSlab = 
case
	when @Age between 0 and 1 then '0-1'
	when @Age between 2 and 4 then '2-4'
	when @Age between 5 and 9 then '5-9'
	when @Age between 10 and 19 then '10-19'
	when @Age >= 20 then '20+'
	else 'Unknown'
end

select @AgeSlab

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks webfred, this helped

Ewan Gilby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources