Mysql – Total hours worked per employee per day including overnight sessions

aggregateMySQLrunning-totalssum

I have a table like this:

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |      state      | 
+--------------+---------------------+-----------------+
(      4       | 2014-03-01 11:12:00 |         0       )  
(      5       | 2014-03-01 12:28:06 |         0       )
(      4       | 2014-03-01 12:50:07 |         1       )
(      4       | 2014-03-01 13:38:00 |         0       )
(      5       | 2014-03-01 13:28:06 |         1       )
(      4       | 2014-03-01 18:42:15 |         1       )
(      4       | 2014-03-02 08:11:08 |         0       ) 
(      4       | 2014-03-02 13:26:11 |         1       ) 
(      5       | 2014-03-02 14:16:15 |         0       ) 
(      4       | 2014-03-02 16:16:15 |         0       ) 
(      5       | 2014-03-02 17:48:21 |         1       ) 
(      4       | 2014-03-02 19:39:03 |         1       ) 
(      5       | 2014-03-02 20:16:15 |         0       )
(      5       | 2014-03-03 04:16:15 |         1       )
+--------------+---------------------+-----------------+

primary key omitted for brevity – please see http://www.sqlfiddle.com/#!9/30503/1

0 means login – 1 means logout. There can be several logins/logouts per user per day or period of days if for example an employee works overnight (as employee 5 does between 2014-03-02 and 2014-03-03 )

I am trying to get the total sum of hours worked per employee per day/shift (e.g. some shifts will span over two days):

1      2014-03-02   total hours worked    08:32:00

I came up with this query:

SELECT CONCAT(
MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked, 
empid, 
Date(time)
FROM emplog T
GROUP BY empid, Date(time)

Which only gave me the time elapsed between the min login and max logout.

From an earlier question I learned how to get the total working hours per employee per day which does not include night shifts:

select empid, work_dt,
    SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time) 
           from emplog as b 
           where a.empid = b.empid 
             and date(a.time) = date(b.time)
             and b.time >= a.time 
             and b.state = 1
          ), now()) as logout
from emplog as a 
where a.state = 0
) as t
group by empid, work_dt;

Still haven't figured out a way to factor in night shifts – looking forward to your input.

There will be employees working during the weekend. Holidays will not be a problem; neither will daylight saving time be.

The application does not have a check in place to prevent out of sync clock punches? For example, clock in/out records out of time-sequence or duplicated due to employee error. I already took care of those kinds of problems in the code I wrote to connect with the device and retrieve the raw data – might be better to handle all the calculations in my code.

Night shifts count 100 % to the day the shift started on. For example, if you start work on 02.01 before midnight, the total working hours are saved with the 02.01 as the date. If you start after midnight, the total working hours are saved with the 03.01 as date.

If there is no matching logout I manually log the user out with a predefined number of hours in my code that prepares the raw data for the MySQL database.

Best Answer

Please check if the performance is sufficient:

SELECT empid
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid;

Edit

To aggregate per day:

SELECT empid
      ,DATE(time) day
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid, DATE(time);

Related Solutions

Mysql – Database Design – Employee Clocking In and Out System

One thing you might want to consider changing is making clocking.clock_out_date nullable. Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in. If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in. You should be able to get a reasonable number for the longest expected shift. It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table. It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.

Mysql – Total hours worked per employee per day

If we assume that login and logout always comes in pairs, and that login and logot are always on the same date, it's just a matter of finding the smallest time for a logout after each login. If no logout is found it means that the employee is still working so now() (if the report is run at a later date, use 23:59:59 for the date of login) is used instead:

select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time) 
           from emplog as b 
           where a.empid = b.empid 
             and date(a.time) = date(b.time)
             and b.time >= a.time 
             and b.state = 1
          ), now()) as logout
from emplog as a 
where a.state = 0

;

Now all that remains is to sum the difference between logout and login for each employee and work_dt:

select empid, work_dt,
        SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
    select empid, date(time) as work_dt, time as login
        , coalesce(
              (select min(time) 
               from emplog as b 
               where a.empid = b.empid 
                 and date(a.time) = date(b.time)
                 and b.time >= a.time 
                 and b.state = 1
              ), now()) as logout
    from emplog as a 
    where a.state = 0
) as t
group by empid, work_dt
;

Best Answer

Related Solutions

Mysql – Database Design – Employee Clocking In and Out System

Mysql – Total hours worked per employee per day

Related Question