CASE Function in SQL Server 2005 - part IV

In Part I, part II and part III of this article series, we saw how to use simple case expressions in queries. In this installment, I illustrate how to use case functions in clauses like 'group by'.

Method 7: Usage of the simple case function in GROUP BY clause

Let us assume we have the following table.

set quoted_identifier off
go
use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50), 
 [Last name] varchar(50), Salary money, state char(2))
go
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (1,'John','Smith',120000,'WA')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (2,'James','Bond',95000,'OR')
insert into Emp (id,[First name],[Last name], salary , 
 State) values (3,'Alexa','Mantena',200000,'WY')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (4,'Shui','Qui',36000,'CO')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (5,'William','Hsu',39000,'NE')
insert into Emp (id,[First name],[Last name], salary , 
 State) values (6,'Danielle','Stewart',50000,'TX')
insert into Emp (id,[First name],[Last name], salary , 
 State) values (7,'Martha','Mcgrath',400000,'PA')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (8,'Henry','Fayol',75000,'NJ')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (9,'Dick','Watson',91000,'NY')
insert into Emp (id,[First name],[Last name], salary, 
 State ) values (10,'Helen','Foster',124000,'AK')
go

Let us assume that we have six tables that hold the IDs of employees that belong to different time zones, as shown below.

if exists (select * from dbo.sysobjects 
 where id = object_id(N'[eastern]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [eastern]
go
create table eastern (id int)
if exists (select * from dbo.sysobjects 
 where id = object_id(N'[mountain]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [mountain]
go
create table mountain (id int)
if exists (select * from dbo.sysobjects 
 where id = object_id(N'[hawaii]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [hawaii]
go
create table hawaii (id int)
if exists (select * from dbo.sysobjects 
 where id = object_id(N'[central]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [central]
go
create table central (id int)
if exists (select * from dbo.sysobjects 
 where id = object_id(N'[alaskan]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [alaskan]
go
create table alaskan (id int)
if exists (select * from dbo.sysobjects 
 where id = object_id(N'[pacific]') 
 and objectproperty(id, N'isusertable') = 1)
drop table [pacific]
go
create table pacific (id int)
go
insert into pacific (id) values (1)
insert into pacific (id) values (2)
insert into mountain (id) values (3)
insert into mountain (id) values (4)
insert into central (id) values (5)
insert into central (id) values (6)
insert into eastern (id) values (7)
insert into eastern (id) values (8)
insert into eastern (id) values (9)
insert into alaskan (id) values (10)
go

If we want to know all of the employees belonging to Eastern time zone, we would probably execute a query similar to the one below.

select e.id,[First Name],[Last name], Salary, State
from emp e join eastern ee on e.id=ee.id

This would produce the following result, as shown below.

id First name  Last name       salary         Timezone
--------------------------------------------------------
7 Martha Mcgrath  400000.00 PA
8 Henry Fayol  75000.00 NJ
9 Dick Watson  91000.00 NY

Now let us assume we want to create a script that would accept a time zone in a variable and show the result based on the variable’s value. This could be achieved using in clause and case function as showb below.

declare @group varchar(10)
set @group='Pacific'
select ee.id,ee.[First Name],ee.[Last Name],Salary, State, @group as TimeZone from emp ee
left join mountain m on m.[id]=ee.[id]
left join alaskan a on a.[id]=ee.[id]
left join hawaii h on h.[id]=ee.[id]
left join central c on c.[id]=ee.[id]
left join pacific p on p.[id]=ee.[id]
left join eastern e on e.[id]=ee.[id]
where ee.id in ( case @group 
when 'Eastern' then e.id
when 'Mountain' then m.id
when 'Pacific' then p.id
when 'Alaskan' then a.id
when 'Hawaii' then h.id
when 'Central' then c.id
end)

This would produce the following result, as shown below.

id First name Last name       salary      state   TimZone 
----------------------------------------------------------------
1 John Smith  120000.00 WA Pacific
2 James Bond  95000.00 OR Pacific

The above script could be written as a procedure:

create procedure emp_timezone @Zone varchar(10)
as
select ee.id,ee.[First Name],ee.[Last Name],Salary, State, @Zone as TimeZone from emp ee
left join mountain m on m.[id]=ee.[id]
left join alaskan a on a.[id]=ee.[id]
left join hawaii h on h.[id]=ee.[id]
left join central c on c.[id]=ee.[id]
left join pacific p on p.[id]=ee.[id]
left join eastern e on e.[id]=ee.[id]
where ee.id in ( case @Zone
when 'Eastern' then e.id
when 'Mountain' then m.id
when 'Pacific' then p.id
when 'Alaskan' then a.id
when 'Hawaii' then h.id
when 'Central' then c.id
end)

Let us execute the procedure, as shown below.

exec emp_timezone 'Eastern'

This would produce the following result, as shown below. [Fig 1.0]

id First name Last name       salary      state   TimZone 
----------------------------------------------------------------
7 Martha Mcgrath  400000.00 PA Eastern
8 Henry Fayol  75000.00 NJ Eastern
9 Dick Watson  91000.00 NY Eastern

Conclusion

Part I, Part II, Part III and Part IV illustrated the usage of case expressions in SQL Server in various situations. Part IV illustrated the usage of the case function in the IN Clause, as mentioned in the beginning of the article.

» See All Articles by Columnist MAK