quote:

---

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

---

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

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

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

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/

---

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.

---