Please navigate to the bottom of the page for Table of Contents

Monday, June 13, 2011

SQL CASE statement examples

It is quite difficult to write a stored procedure or a function if you do not know or understand the SQL conditional processing statements. From an interview perspective, you should familiarize yourself with the conditional processing as well as the flow control statements.

The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it allows an alternative value to be displayed depending on the value of a column. A common use of the CASE expression is to replace codes or abbreviations with more readable values. This is also known as a Simple CASE expression.

Question: For a given product and category abbreviation, show the full category name using a CASE expression.

SELECT   ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Product
ORDER BY ProductNumber;


Another use of CASE is to categorize data.

Question: Based on an item list price, show the price range for the item. The example below shows a searched CASE expression which evaluates a set of Boolean expressions to determine the result.

SELECT   ProductNumber, Name, 'Price Range' = 
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;


A CASE statement can also be used in a similar way as an Acess Iff:

Question: Show special instructions if they exist for a customer.

SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
CASE
WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
ELSE TelephoneSpecialInstructions
END
FROM Person.vAdditionalContactInfo;


CASE Advanced scenarios


In addition to being used in a SELECT statement, a CASE expression can also be used in ORDER BY and HAVING clauses and in UPDATE and SET statements. Let’s review those examples.

 

Using CASE in an ORDER BY clause


Question: Sort of list of sales people by territory when they serve US otherwise sort by country.

The following example uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. In the example below the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.


SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;


Using CASE in a HAVING clause


Question: Find the maximum hourly rate for each job title and then restrict the result to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the results further.


SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;


Using CASE in an UPDATE statement


Question: Write an UPDATE statement to update the vacation hours for salaried employees. If the employee has less than 10 hours of vacation, give her 40 otherwise 20.

UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
WHERE SalariedFlag = 0;

3 comments:

  1. kindly provide table details with this examples..

    ReplyDelete
  2. Good post Nikhil, thanks!

    A nice one on same topic of case statement in sql here:

    SQL Case Statement

    ReplyDelete