MySQL – Calculate Total Hours Worked Per Employee Per Day

gaps-and-islandsMySQLsum

I have a table like this:
SQLfiddle: http://www.sqlfiddle.com/#!9/340c8

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

+--------------+---------------------+-----------------+
|    empid     |       time          |         state   | 
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | 0               |
|            1 | 2011-08-16 15:00:06 | 1               |
|            1 | 2011-08-16 15:14:51 | 0               |
|            2 | 2011-08-16 15:15:00 | 0               |
|            1 | 2011-08-16 15:59:01 | 1               |
|            2 | 2011-08-16 15:59:09 | 1               |
+--------------+---------------------+-----------------+

Where 0 means login and 1 logout.

I am trying to get the total hours worked per employee per day:

1      2011-08-16     total hours worked    08:32:00

With my 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)

I get the time worked per day, but only for the min max in out times so I am missing all employees that come in and out several times per day. Any ideas?

Best Answer

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
;
Related Question