Question 231114 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Fetch record of those employee who are on leave for 11 days in a row

I have an Attendance record having fields employee code, status, attendance_date.
I want to fetch those employee record who are on leave continuously for 11 days using Sql query
The condition here is if employee attendance status is continuously leave or absent or unmarked attendance(those date record which are not present in attendance doctype) for 11 days including weekend also.

The condition can be mix of all status i.e leave, absent or unmarked attendance

Tags:

2

Answers


  1. 0

    Please check the query given below.

    SELECT 
  employee_code,
  COUNT(attendance_date) AS days_on_leave 
FROM attendance
WHERE 
  status IN ('Leave', 'Absent', 'Unmarked Attendance') 
GROUP BY employee_code
  HAVING COUNT(attendance_date) >= 11;
    Login or Signup to reply.
  2. 0

    This query finds employees who have 11 consecutive days of ‘Leave’, ‘Absent’, or ‘Unmarked’ status in their attendance records. It connects employee data with attendance data, filters for the specific statuses and date range, groups the data, and then selects only those groups where the count of records is 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 ('Leave', 'Absent', 'Unmarked')
GROUP BY a.employee_code, a.attendance_date
HAVING COUNT(*) = 11;

    If this query doesn’t work for you, kindly share your schema. Please identify mistake if there is any in this query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search