Get employees who have not clocked in for 11 consecutive days

Question

I am trying to get employees from database who have not marked attendance for 11 consecutive days, For this I have employee table and attendance table but the problem I have with this is that there is no record in the attendance table so how can I get

I tried many queries, some of them are as follows:

SELECT e.name, e.full_name
FROM tabEmployee e
LEFT JOIN (
    SELECT
        employee, employee
        MIN(attendance_date) AS first_attendance_date
    FROM tabAttendance
    GROUP BY employee
) ar ON e.name = ar.employee
WHERE ar.first_attendance_date IS NULL OR
      NOT EXISTS (
          SELECT 1
          FROM tabAttendance
          WHERE employee = e.name
          AND attendance_date >= ar.first_attendance_date
          AND attendance_date < DATE_ADD(ar.first_attendance_date, INTERVAL 11 DAY)
      )

Another query:

SELECT e.name, COUNT(a.`attendance_date`), COUNT(e.`name`) from tabEmployee e
LEFT JOIN tabAttendance a ON e.name = a.`employee`
WHERE a.`employee` IS NULL
GROUP BY e.`name`

Answer 1

Using LAG() analysis function, we can try:

WITH cte AS (
    SELECT e.name, e.full_name,
           LAG(a.attendance_date) OVER (PARTITION BY a.employee
                                        ORDER BY a.attendance_date) AS lag_attendance_date
    FROM tabAttendance a
    INNER JOIN tabEmployee e ON e.name = a.employee
)

SELECT DISTINCT name, full_name
FROM cte
WHERE DATEDIFF(attendance_date, lag_attendance_date) > 11;

The basic strategy here is to generate the lag (previous consecutive value) of the attendance date in the CTE. We then report only employees with a gap of 11 days or longer.