Get records of employees who took 11 consecutive days off

Question

I have an attendance record with fields for employee number, status and attendance date. I want to use SQL query to get those records of employees who have taken leave for 11 consecutive days. The condition is that if the employee's attendance status is leave, absence or unmarked attendance (a date record that does not exist in the attendance document) for 11 consecutive days, including weekends.

The criteria can be a mixture of all statuses, i.e. leave, absence or unmarked attendance.

Answer 1

This query finds employees who have "leave", "absent" or "unmarked" status in the attendance record for 11 consecutive days. It joins employee data with attendance data, filters for specific status and date range, groups the data and selects only those groups with record count of 11.

SELECT DISTINCT a.employee_code
FROM employees e
JOIN attendance a ON e.employee_code = a.employee_code
WHERE a.attendance_date BETWEEN a.attendance_date AND DATE_ADD(a.attendance_date, INTERVAL 10 DAY)
  AND a.status IN ('请假', '缺席', '未标记')
GROUP BY a.employee_code, a.attendance_date
HAVING COUNT(*) = 11;

If this query doesn't work for you, please share your pattern. If there are any errors in this query, please point it out.