Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Basic SQL Reports in SQL Server

Introduction
Custom classifications of business objects
3. Custom classifications – exercise
Custom grouping of business objects
Custom counting of business objects
Summary

Instruction

Good job! It's time to write your first query using CASE WHEN.

Exercise

We want to create a report measuring the level of experience each Northwind employee has with the company. Show the FirstName, LastName, HireDate, and Experience columns for each employee. The Experience column should display the following values:

  • N'junior' for employees hired after January, 1st 2014.
  • N'middle' for employees hired after January, 1st 2013 but before January, 1st 2014.
  • N'senior' for employees hired on or before January, 1st 2013.

Stuck? Here's a hint!

Use the following CASE WHEN template in the SELECT clause:

CASE
  WHEN ... THEN N'junior'
  WHEN ... THEN N'middle'
  WHEN ... THEN N'senior'
END AS Experience

You can check if an employee was hired after January, 1st 2014 in the following way:

HireDate > '2014-01-01'