SQL: Case Statement

The Case Statement is SQL’s version of IF/THEN logic statements.

In this example, I am using the AdventureWorks2012 database, which is free to download. You do not need this to follow the lesson, but if you want to play along, follow this link: SQL: SELECT Statement

 

Let’s start by looking at our data, using the following Select Statement:

SELECT 
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,[BirthDate]
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate] 
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here is the output

sqlagg3

Now what if you don’t really care about the particular employee birthday, you simply want to know who is over 40 and who is 40 or younger.

To do that, we need a mechanism to determine an age above or below 40 from the birth date. In most programming languages, you would simply use an If / Then statement. In SQL, we need to use a Case Statement.

The Case Syntax is simple. Let’s assume a variable A, set to either 1 or 0 representing True or False.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
End

Note the statement is started with the keyword Case and closed with the keyword End.

In the middle, the logic is controlled using When and Then. When <condition> Then  <Action>

If needed, you can append an Else to your statement to cover conditions not specified in your statements.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
     else "Neither" 
End

 

 

Now, let’s apply it to our case above. Notice the Case statement is put right into my Select statement just as if it were a common field.

select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,
 case 
    when BirthDate < '1977-01-01' then 'Over40'
    when BirthDate >= '1977-01-01' then 'Under40'
 end
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here are my results. You will note a new column with values Over40 or Under40

2017-01-09_11-29-42.jpg

But what does that column mean? Let’s name it to make it clear.  After the End keyword add as Over40

 select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle],
 case 
        when BirthDate < '1977-01-01' then 'Over40'
         when BirthDate >= '1977-01-01' then 'Under40'
 end as Over40,
 [MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here, now the column has a name.

2017-01-09_11-32-09.jpg

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s