Total hours worked per employee per day

Question

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?

Lennart - Slava Ukraini · Accepted Answer · 2016-02-21T09:22:13.013

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
;

Total hours worked per employee per day

1 Answers1

Linked