Get employees who have not clocked in for 11 consecutive days-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Get employees who have not clocked in for 11 consecutive days

P粉103739566 2023-08-18 09:31:19

397

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`

P粉103739566

reply all (1)

P粉2310799762023-08-19 07:58:16 1 floor

UsingLAG()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.