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`
Using
LAG()
analysis function, we can try: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.