Calculate absence time in day for employee

Question

I have table to store attendance data of my employee as below:

EmployeeID|   Date   |EnterTime|ExitTime|
    1     |2017-01-01|07:11:00 |15:02:00|
    2     |2017-01-01|07:30:00 |12:00:00|
    2     |2017-01-02|07:00:00 |15:00:00|
    1     |2017-01-02|07:30:00 |10:00:00|
    1     |2017-01-02|11:20:00 |15:00:00|
    1     |2017-01-03|09:30:00 |10:00:00|
    1     |2017-01-03|11:20:00 |15:00:00|

Actually my working time is from 07:00:00 to 15:00:00 .

I want To sum up the absence time of each employee for example : in 2017-01-01 for employee ID 2 , is 03:00:00 hours as absence and in date 2017-01-02 for employee ID 1 is 01:00:00 hour. In Date 2017-01-03 , employee id 1 , is 02:30:00 and employee id 2 absence is 08:00:00 hour because there is no log in my table. Finally need following report:

EmployeeID|TotalWorkingHour       |TotalAbsenceHour|
    1     |sum(EnterTime-ExitTime)|05:09:00        |
    2     |sum(EnterTime-ExitTime)|11:30:00        |

I get total working seconds by following select,but don't know how to calculate absence seconds:

select EmployeeID,
sum(datediff(second,Convert(DATETIME, EnterTime, 114), Convert(DATETIME, ExitTime, 114)) ) as TotalWorkingSeconds 
from Attendance
where Attendance.Date between @FromDate and @ToDate 
and (EnterTime is not null) 
and (ExitTime is not null)
group by EmployeeID

Surely once you've got "presence time", "absence time" is just 8 hours - presence time, isn't it? — Jon Skeet, Jun 21 '17 at 07:18
could you please post your current SQL stament? what have you tried so far? did you put any efford in solving your problem already? — Esteban P., Jun 21 '17 at 07:22
sum(EnterTime-ExitTime) ? why would you want enter - exit?? 9:00-17:00 surely gives -8, youd want exittime-entertime? — BugFinder, Jun 21 '17 at 07:24
@JonSkeet you are right but as mentioned in my post some days my employee didn't attend at all , so how to find those days? — MahdiIBM, Jun 21 '17 at 08:03
@MahdiIBM: Well for those days, you'd have a present-time of 0, or no record at all, depending on whether you use a left join or not. It still feels simpler to only compute present time and use that as a basis for absent time, rather than trying to sum individual absences. — Jon Skeet, Jun 21 '17 at 08:04
@JonSkeet please let me know how to find the days that employee don't have record also consider if all employee are not peresented!! — MahdiIBM, Jun 21 '17 at 08:11
What do you mean "also consider if all employee are not represented" - how is that different from "that employee don't have record"? Basically this sound like you need a left join on the employee table, so that you can get the values for *all* employees, regardless. — Jon Skeet, Jun 21 '17 at 08:13
@JonSkeet Imagine we have 5 employee and today is 2017-01-01 and only 4 employee attended to work but for tomorrow no body attended to work , so for tomorrow we should calculate absence for all of our employees. and for 2017-01-01 absence should be calculate for one person only . — MahdiIBM, Jun 21 '17 at 08:24
Well you should presumably *calculate* the absent time (and it may well be 0) for all employees on every day. But yet again, this just sounds like applying a left join, and using a default of "If I don't have any records, that means a present_time of 0." — Jon Skeet, Jun 21 '17 at 08:25
Is this related to C# and/or Visual Studio in any way? If not, please remove those labels. Also, please show us what you've tried thus far. You almost certainly need an "employees" table so you can use an outer join to show employees, regardless of whether they check in on a given day, and logic to define a working day - do weekends count? How about bank holidays? — Neville Kuyt, Jun 21 '17 at 08:49

score 0 · Accepted Answer · edited Jun 21 '17 at 09:04

It might be not the final and perfect solution, but it works out:

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate = '2016-12-31'
SET @EndDate =  '2017-01-05' -- GETDATE()

SELECT alldate_and_employee.EmployeeID
      ,alldate_and_employee.Date
      ,COALESCE(employee_workingdays.WorkingMinutes,0) as TimeWorked
      ,(alldate_and_employee.PlannedWorkingTime - COALESCE(employee_workingdays.WorkingMinutes,0)) as  WorkedTooLessMinutes
  FROM
  (
     -- returns a table with a combination of all Employees and all Dates
     SELECT DISTINCT EmployeeID, datelist.Date, 480 as PlannedWorkingTime
       FROM mytable
      CROSS JOIN
      (
        -- selects all dates between @StartDate and @Enddate
        SELECT DATEADD(DAY,number+1,@StartDate) [Date]
        FROM master..spt_values
        WHERE type = 'P'
        AND DATEADD(DAY,number+1,@StartDate) < @EndDate
      ) datelist
  ) alldate_and_employee
  LEFT OUTER JOIN
  (
      -- groups the working time of each employee for a working day
      SELECT EmployeeID
           ,Date 
           ,SUM(DATEDIFF(minute, EnterTime, ExitTime)) as WorkingMinutes
        FROM mytable
    GROUP BY EmployeeID
            ,Date 
  ) employee_workingdays
  ON employee_workingdays.Date       = alldate_and_employee.Date
 AND employee_workingdays.EmployeeID = alldate_and_employee.EmployeeID

ORDER BY alldate_and_employee.Date, alldate_and_employee.EmployeeID

Calculate absence time in day for employee

1 Answers1