Example: Producing a Report to Show Employee Salary - Advanced SQL Engine - Teradata Database

All employees whose salary is less than $40000 are eligible for an across the board pay increase.

The following SELECT statement uses a CASE expression to produce a report showing all employees making under $40000, displaying the first 15 characters of the last name, the salary amount (formatted with $and punctuation), the number of years of service based on the current date (in the column named On_The_Job) and which of the four categories they qualify for: '15% Increase', '10% Increase', '05% Increase' or 'Not Qualified'.

   SELECT CAST(last_name AS CHARACTER(15))
      ,salary_amount (FORMAT '$,$$9,999.99')
      ,(date - hire_date)/365.25 (FORMAT 'Z9.99') AS On_The_Job
      ,CASE 
          WHEN salary_amount < 30000 AND On_The_Job > 8 
          THEN '15% Increase'
          WHEN salary_amount < 35000 AND On_The_Job > 10 
          THEN '10% Increase'
          WHEN salary_amount < 40000 AND On_The_Job > 10
          THEN '05% Increase'
          ELSE 'Not Qualified'
         END  AS Plan
   WHERE salary_amount < 40000
   FROM employee
   ORDER BY 4;

The result of this query appears in the following table: