3

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.

  • What if there's no matching logout? Using NOW() instead might lead to totally wrong data, how about setting it to a default number of hours like 8? – dnoeth Feb 28 '16 at 10:54
  • If there is no matching logout I manually log out the user in my code that prepares the raw data for the mysql database with a predefined number of hours. – vibration Feb 28 '16 at 11:17
  • Then removing the and date(a.time) = date(b.time) will return the correct result, same as Olli's answer :-) – dnoeth Feb 28 '16 at 11:35
4

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.