取得連續11天沒有打卡的員工

Question

我正在嘗試從資料庫中獲取連續11天沒有標記出勤的員工， 為此我有員工表和出勤表，但是我在這方面遇到的問題是出勤表中沒有記錄，所以我該如何獲取

我嘗試了很多查詢，其中一些如下：

SELECT e.name, e.full_name
FROM tabEmployee e
LEFT JOIN (
    SELECT
        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)
      )

另一個查詢：

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

使用 LAG() 分析函數，我們可以嘗試：

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;

這裡的基本策略是在 CTE 中產生出勤日期的 lag（前一個連續值）。然後我們只報告有 11 天或更長間隔的員工。