MySQL – Hours Worked Per Day Computation via Bio Logs

MySQL

This is my checkinout table:

USERID  CHECKTIME            CHECKTYPE
------  -------------------  ---------
404     2016-02-12 07:24:03  I
404     2016-02-12 17:01:34  O
404     2016-02-15 07:24:21  I
404     2016-02-15 17:00:36  I
404     2016-02-16 07:09:01  I
404     2016-02-16 17:01:33  O

I used the script below to compute hoursworked/day, but the output gives my column Hoursworked huge value:

SELECT userid,Name
    DATE(CHECKTIME) AS date,
    SUM(UNIX_TIMESTAMP(CHECKTIME)*(1-2*checktype))/3600 AS hours_worked
    FROM CHECKINOUT
    GROUP BY date(CHECKTIME), userid;

Output:

Hoursworked  Userid  Date(Checktime)
-----------  ------  ---------------
NULL         404     2016-02-12
NULL         404     2016-02-12
NULL         404     2016-02-15
NULL         404     2016-02-15
NULL         404     2016-02-16
NULL         404     2016-02-16

How to resolve this issue?

My data source is inconsistent: our employee has the habit of multiple login\logout. Can you help where to eliminate the employee with incomplete I\O and for those having multiple login\out to get the first and last login\out logs.

Best Answer

The idea I wrote in the comment http://sqlfiddle.com/#!9/785bc/5 :

select 
    a.userid,
    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as diff
from CHECKINOUT a
join CHECKINOUT b
    on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid
where
    a.CHECKTYPE = 'I'
    AND b.CHECKTYPE = 'O'
group by date(a.CHECKTIME), userid;

But as I wrote, there is a problem with the days where one of the I/O rows is missing and you need to either fix your data or specify what to do in such case.

Related Question