0% completed
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
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.
- The
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.
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible