SQL query to get the dates an employee was absent

Question

I have a table which stores the transactions of employees' ID cards swiped in and swiped out. Columns: Employee ID, employee name, swipe in date and time, swipe out date and time.

I need a working MS SQL Server query to get an employee ID and the dates they were absent.

(Note: Additional table(s) may be created in order to answer the question.)

You will probably need to use a calendar table for this which contains all the work days. Then join to this table to find absences. — Tim Biegeleisen, Jan 26 '17 at 04:48
@TimBiegeleisen is right..you need all holiday days need to store in another table..after you join both tables.. — Darshak, Jan 26 '17 at 05:49
The least you should do is add the tag for the RDBMS you are using, provide some sample data (DDL + DML is best) and desired results. Otherwise that's just 50 reputation points thrown away. — Zohar Peled, Jan 29 '17 at 13:14
select e.Emp_ID as absentId,a.Date as date from employeelist e join attendence_info a on e.Emp_ID=a.Emp_ID order by a.Date — Vishe, Jan 30 '17 at 06:24

score 7 · Accepted Answer · edited May 23 '17 at 11:45

As mentioned by others, it will help to have a table storing all working days. For simplicity, let's say this is named business_calendar and stores all working day dates in a DATETIME column named working_day. As I don't know which version of SQL Server you're using I'll use DATETIMEs throughout - so those that only represent dates will have no time part. This answer is useful to consult on how to remove the time part when making comparisons.

To err on the safe side, would suggest only querying for dates where there's no record of an employee swiping in or out - this may cater for edge cases such as the employee working beyond midnight or perhaps there might be NULLs if for some reason one of the swipe in or swipe out didn't register.

To find the absent days for a single employee:

DECLARE @emp_id INT;
SET @emp_id = 1;

SELECT @emp_id AS employee_id, bc.working_day AS absent_day
FROM business_calendar bc
WHERE NOT EXISTS (
    SELECT * 
    FROM attendance a
    WHERE a.employee_id = @emp_id
      AND (DATEADD(day, DATEDIFF(day, 0, a.swipe_in), 0) = bc.working_day
           OR DATEADD(day, DATEDIFF(day, 0, a.swipe_out), 0) = bc.working_day));

...Or to get a list of all employees and dates they were absent, a CROSS JOIN can be used:

SELECT e.id AS employee_id, bc.working_day AS absent_day
FROM business_calendar bc
CROSS JOIN employee e
WHERE NOT EXISTS (
    SELECT * 
    FROM attendance a
    WHERE a.employee_id = e.id 
      AND (DATEADD(day, DATEDIFF(day, 0, a.swipe_in), 0) = bc.working_day
           OR DATEADD(day, DATEDIFF(day, 0, a.swipe_out), 0) = bc.working_day));

This could of course be easily filtered to only include a subset of the employees and/or dates by ANDing further WHERE conditions.

Demo here: http://rextester.com/VPTTM33285

What's the business days table???? this solution was not cleared anymore , could you please explain it to others users? — , Mar 04 '21 at 16:46
@csharp_devloper31 Think it's fairly clear from the first two sentences of the answer + [the demo](https://rextester.com/VPTTM33285)? — Steve Chambers, Mar 05 '21 at 15:11

score 0 · Answer 2 · edited May 23 '17 at 12:01

Based on assumptions that I made (only swipe_in, ignoring swipe_out), this ought to work. Also assumed date range to be that of a month here. I have used the example for table generation from this.

DECLARE @StartDate DATETIME 
SET @StartDate = DATEADD(Month,-1,GetDate())
Declare @EndDate DATETIME
SET @EndDate = Getdate()

SELECT * From (
SELECT * FROM EmployeeAttendance EA
RIGHT OUTER JOIN (
SELECT  DATEADD(DAY, nbr - 1, @StartDate) as CalenderDate
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
) A1 ON (Dateadd(dd,0,Datediff(dd,0,A1.CalenderDate)) = DateAdd(dd,0,Datediff(dd,0,EA.SwipeIn))))A2
WHERE Name IS NULL

SQL query to get the dates an employee was absent

2 Answers2