Kata

In a large corporation, the Human Resources (HR) department is responsible for managing employee attendance. They've observed a pattern of prolonged absenteeism among some employees and want to identify these cases to address potential underlying issues, such as job dissatisfaction, personal problems, or health issues. The goal is to improve employee well-being and productivity. The HR department has compiled an attendance record and requires a database query to pinpoint employees who have been absent in at least three consecutive months.

You are provided with a database table named attendance which records the dates on which employees were absent. Your task is to write an SQL query that identifies employees who have been absent in at least three consecutive calendar months. The query should return the details of these absences.

attendance:

• emp_no (integer): The employee number.
• absent_date (Date): The date of absence.

Requirements:

1. Calculate Consecutive Absences: The query should identify sequences of months in which an employee was absent. A sequence is defined as a period where there are no gaps of more than one month between absences.
2. Filter Employees: Only include employees who have been absent in at least three consecutive months.
3. Return Data: The output should contain the employee number (emp_no) and the date of absence (absent_date). The output should exclusively contain the records of absences that fall within the periods where an employee has been absent for at least three consecutive calendar months. This means each returned record should be part of a sequence of absences spanning a minimum of three consecutive months.
4. If an employee has more than one period where they were absent in three or more consecutive months, each period should be treated independently. The query should return records from all such periods.

The returned records should be ordered first by emp_no in descending order and then by absent_date in ascending order.

Note: In the provided attendance data, each employee is recorded only once per date, ensuring no duplicate dates or double shifts for the same employee. This unique entry per employee per day must be considered in your analysis.

For this sample data:

| emp_no | absent_date |
|--------|-------------|
| 111    | 2024-03-01  |
| 111    | 2024-05-05  |
| 222    | 2024-02-13  |
| 222    | 2024-03-01  |
| 222    | 2024-03-02  |
| 222    | 2024-04-29  |
| 222    | 2024-09-09  |
| 333    | 2024-05-15  |
| 333    | 2024-09-18  |
| 333    | 2024-09-19  |

Expected Output is the following:

| emp_no | absent_date |
|--------|-------------|
| 222    | 2024-02-13  |
| 222    | 2024-03-01  |
| 222    | 2024-03-02  |
| 222    | 2024-04-29  |

Employee 222: Absent in February, March, and April. These months are consecutive, meeting the criteria. Thus, all records of employee 222 during these months are included in the output.

Employee 111: Although absent in March and May, there is a gap of more than one month (April) between the absences. Therefore, this employee's records are not included.

Employee 333: Similar to employee 111, the absences are in May and September, with a gap of several months in between. Hence, their records are also not included.

GLHF!