22. Employee Attendance Record

Problem

Table: Employees


+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
+---------------+---------+
employee_id is the primary key for this table.
Each row of this table contains the ID and the name of one employee in the company.

Table: Attendance


+---------------+---------+------+
| Column Name   | Type    | Note |
+---------------+---------+------+
| employee_id   | int     |      |
| attendance_date | date  |      |
| status        | varchar |      |
+---------------+---------+------+
There is no primary key for this table, it may contain duplicates.
Each row of this table records the fact that the employee with ID employee_id had a status ('Present', 'Absent', 'Late') on a specific attendance_date.

Problem Definition

Write a solution to find the attendance record for each employee.

Return the result table ordered by employee_id.

Example


Input: 
Employees table:
+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
| 101         | Alice         |
| 102         | Bob           |
| 103         | Charlie       |
+-------------+---------------+


Attendance table:
+-------------+----------------+--------+
| employee_id | attendance_date | status |
+-------------+----------------+--------+
| 101         | 2020-11-01      | Present|
| 101         | 2020-11-02      | Absent |
| 102         | 2020-11-01      | Late   |
| 102         | 2020-11-02      | Present|
| 103         | 2020-11-01      | Present|
| 103         | 2020-11-02      | Present|
+-------------+----------------+--------+


Output: 
+-------------+---------------+--------------+-------------+-----------+
| employee_id | employee_name | days_present | days_absent | days_late |
+-------------+---------------+--------------+-------------+-----------+
| 101         | Alice         | 1            | 1           | 0         |
| 102         | Bob           | 1            | 0           | 1         |
| 103         | Charlie       | 2            | 0           | 0         |
+-------------+---------------+--------------+-------------+-----------+

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

To address this problem, SQL queries are employed to extract and process information from the Employees and Attendance tables. The goal is to find the attendance record for each employee, including the number of days present, absent, and late.

The approach begins with a LEFT JOIN between the Employees and Attendance tables, using employee_id to link them. This join is essential for associating employee names with their attendance records.

Following the join, the SUM function, in combination with CASE statements, is used to calculate the number of days each employee was present, absent, and late. This is done by incrementing the count for each status accordingly.

The GROUP BY clause groups the results by employee_id and employee_name, which is necessary for aggregating the attendance records for each employee.

Finally, the result is ordered by employee_id using the ORDER BY clause, as specified in the problem statement.


SELECT e.employee_id,
       e.employee_name,
       SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) AS days_present,
       SUM(CASE WHEN a.status = 'Absent' THEN 1 ELSE 0 END) AS days_absent,
       SUM(CASE WHEN a.status = 'Late' THEN 1 ELSE 0 END) AS days_late
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id
GROUP BY e.employee_id, e.employee_name
ORDER BY e.employee_id;

Step-by-Step Approach

Step 1: Join `Employees` and `Attendance` Tables

Associate each employee with their corresponding attendance records.

SQL Query:


SELECT e.employee_id,
       e.employee_name,
       a.status
FROM Employees e
LEFT JOIN Attendance a ON e.employee_id = a.employee_id;

Output After Step 1:


+-------------+---------------+--------+
| employee_id | employee_name | status |
+-------------+---------------+--------+
| 101         | Alice         | Present|
| 101         | Alice         | Absent |
| 102         | Bob           | Late   |
| 102         | Bob           | Present|
| 103         | Charlie       | Present|
| 103         | Charlie       | Present|
+-------------+---------------+--------+

Step 2: Calculate Attendance Counts

Count the number of days each employee was Present, Absent, or Late.