fbpx

Employees Absence Report

This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes more than 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.

 

The following EmployeeAbsence table registers information about the absences of different employees. Each row specifies :

  • Employee ID – The employee number
  • Absence Type ID – The absence category: (2) Illness or Injury (3) Maternity/Paternity (8) Personal reasons
  • Absence Date – The date of the absence
  • Number of Hours -The number of absence hours for a specific date
CREATE TABLE EmployeeAbsence 
(employee_id int, 
 absence_type_id int, 
 num_of_hours int,
 absence_date date)
GO 

 

So, for example, we can see that :

  • On 2017-01-02, employee number 5 was absent for 9 hours due to medical reasons
  • In overall, employees number 5 was absent twice due to medical reasons (18 hours in total)
  • On 2017-12-13, employee number 7 was absent due to personal reasons
  • In overall, employee number 7 was absent three times due to personal reasons (15 hours in total)

 

 

write a query to pivot the data as follows :



 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…