Example: Producing a Report to Show Employee Salary

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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

IF your salary is less than … AND you have greater than this many years of service … THEN you receive this percentage salary increase …
$30000.00 8 15
$35000.00 10 10
$40000.00 5

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:

last_name salary_amount On_The_Job Plan
Trader $37,850.00 20.61 05% Increase
Charles $39,500.00 18.44 05% Increase
Johnson $36,300.00 20.41 05% Increase
Hopkins $37,900.00 19.99 05% Increase
Morrissey $38,750.00 18.44 05% Increase
Ryan $31,200.00 20.41 10% Increase
Machado $32,300.00 18.03 10% Increase
Short $34,700.00 17.86 10% Increase
Lombardo $31,000.00 20.11 10% Increase
Phillips $24,500.00 19.95 15% Increase
Rabbit $26,500.00 18.03 15% Increase
Kanieski $29,250.00 20.11 15% Increase
Hoover $25,525.00 20.73 15% Increase
Crane $24,500.00 19.15 15% Increase
Stein $29,450.00 20.41 15% Increase