Find Employees Who Have Never Missed a Day of Work

Find Employees Who Have Never Missed a Day of Work Query Using MS SQL Server?

To find employees who have never missed a day of work, you need to compare their attendance records with the expected workdays.

Assuming you have the following tables:

• Employees table with columns: EmployeeID, EmployeeName.
• Attendance table with columns: EmployeeID, AttendanceDate, Status (where a status of 'Absent' or similar indicates a missed day).

Here’s a step-by-step SQL query to find employees who have never missed a day of work:

SQL Query

-- Find employees who have never missed a day of work

WITH EmployeeAttendance AS (

    SELECT

        e.EmployeeID,

        e.EmployeeName,

        COUNT(a.AttendanceDate) AS TotalWorkdays,

        SUM(CASE WHEN a.Status = 'Absent' THEN 1 ELSE 0 END) AS MissedDays

    FROM

        Employees e

        LEFT JOIN Attendance a ON e.EmployeeID = a.EmployeeID

    GROUP BY

        e.EmployeeID, e.EmployeeName

)

SELECT

    EmployeeID,

    EmployeeName

FROM

    EmployeeAttendance

WHERE

    MissedDays = 0

ORDER BY

    EmployeeName;

Explanation

1. EmployeeAttendance CTE:

   • COUNT(a.AttendanceDate) AS TotalWorkdays: Counts the total number of attendance records for each employee.
   • SUM(CASE WHEN a.Status = 'Absent' THEN 1 ELSE 0 END) AS MissedDays: Counts the number of days marked as 'Absent' for each employee.
   • LEFT JOIN Attendance a ON e.EmployeeID = a.EmployeeID: Joins the Employees table with the Attendance table to get attendance data for each employee.
   • GROUP BY e.EmployeeID, e.EmployeeName: Groups the results by EmployeeID and EmployeeName to aggregate attendance data.

2. Main Query:

   • WHERE MissedDays = 0: Filters out employees who have missed any days of work.
   • ORDER BY EmployeeName: Orders the results by employee name for better readability.

Additional Considerations

• Attendance Records: Ensure that the Attendance table has accurate records for all workdays. If you have holidays or non-working days, they should be accounted for accordingly.
• Handling Missing Records: If there are no attendance records for an employee (e.g., they are new or haven't yet been recorded), they will not appear in this result. Adjust the query based on your data requirements and schema.

Adjust the column names and table structure based on your actual schema. This query will help you identify employees who have never missed a day of work based on their attendance records.