Using the TSQL CASE Statement

Here is a quick video tutorial on how to use the T-SQL CASE function on SQL Server 2012, SQL Server 2014 0r newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample or demo code from the video tutorial.

CREATE DATABASE [QueryTraining];
GO

USE [QueryTraining];
GO

------------------------ EXAMPLES SETUP ------------------------
-- Table to be used for training
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);

declare @corners as int = 6;
-- the old way using case.
SELECT CASE @corners
		WHEN 1 THEN 'point'
		WHEN 2 THEN 'line'
		WHEN 3 THEN 'triangle'
		WHEN 4 THEN 'square'
		WHEN 5 THEN 'pentagon'
		WHEN 6 THEN 'hexagon'
		WHEN 7 THEN 'heptagon'
		WHEN 8 THEN 'octagon'
		ELSE NULL
	   END;

-- assume we want to display an indicator to see if we are above
-- or below average. First we start with the average over departmentID
SELECT Year,
       DepartmentID,
       Revenue,
       AVG(Revenue) OVER (PARTITION BY DepartmentID) AS AverageRevenue
  FROM REVENUE
 ORDER BY DepartmentID, year;

-- using the CASE statement we would get the following
SELECT Year, DepartmentID, Revenue, AverageRevenue,
       CASE WHEN Revenue > AverageRevenue
            THEN 'Better Than Average'
            ELSE 'Not Better'
        END AS Ranking
  FROM (SELECT Year, DepartmentID, Revenue,
               avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
          FROM REVENUE
	   ) as t
 ORDER BY DepartmentID, year;

More Info:

• Additional Training on my YouTube Channel
• Free SQL Query Training
• Using IIF 

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!