30. Employee Absences

Problem

Table: Employee


+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| employee_id    | int     |
| employee_name  | varchar |
| start_date     | date    |
| end_date       | date    |
+----------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee's name and the start and end dates of their absence period.
It is guaranteed that start_date <= end_date.

Problem Definition

Write a solution to find all employees who were absent for more than three consecutive days.

Return the result table ordered by employee_name in ascending order.

Example


Input: 
Employee table:
+-------------+---------------+------------+------------+
| employee_id | employee_name | start_date | end_date   |
+-------------+---------------+------------+------------+
| 1           | Alice         | 2020-02-01 | 2020-02-04 |
| 2           | Bob           | 2020-02-05 | 2020-02-09 |
| 3           | Charlie       | 2020-02-10 | 2020-02-11 |
| 4           | David         | 2020-02-15 | 2020-02-20 |
| 5           | Eve           | 2020-02-25 | 2020-02-28 |
+-------------+---------------+------------+------------+


Output: 
+---------------+------------+------------+
| employee_name | start_date | end_date   |
+---------------+------------+------------+
| Alice         | 2020-02-01 | 2020-02-04 |
| Bob           | 2020-02-05 | 2020-02-09 |
| David         | 2020-02-15 | 2020-02-20 |
+---------------+------------+------------+

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 solve this problem, the approach involves using SQL queries to analyze the Employee table and identify employees who were absent for more than three consecutive days. The table contains information about employee absences, including their names, start dates, and end dates of absence periods.

The solution employs a WHERE clause to filter the records based on the condition that the difference between the end_date and the start_date is greater than three days. This ensures that only employees who were absent for more than three consecutive days are included in the result.

The final step involves ordering the result table by employee_name in ascending order, as specified in the problem statement.


SELECT employee_name,
       start_date,
       end_date
FROM   Employee
WHERE  DATEDIFF(end_date, start_date) >= 3
ORDER  BY employee_name ASC;

Let’s break down the query step by step:

Step 1: Filtering Absences Longer Than Three Days

We apply a condition to select only those absences where the duration is more than three days.


WHERE DATEDIFF(end_date, start_date) >= 3

>= 3 Condition: This ensures that employees with exactly 4 days of absence (like Alice) are included, along with those who were absent for more than 3 days.
- The DATEDIFF function calculates the difference in days as: DATEDIFF('2020-02-04', '2020-02-01') = 3. So, >=3 condition is neccessary.

Step 2: Ordering the Result by Employee Name

Finally, we order the results by employee_name in ascending order to align with the problem statement.


ORDER BY employee_name ASC

Final Output:


+---------------+------------+------------+
| employee_name | start_date | end_date   |
+---------------+------------+------------+
| Alice         | 2020-02-01 | 2020-02-04 |
| Bob           | 2020-02-05 | 2020-02-09 |
| David         | 2020-02-15 | 2020-02-20 |
+---------------+------------+------------+

This final result table lists the employees who were absent for more than three consecutive days, sorted by their names.