Best way to query mysql for total time worked in a day

Question

I'm trying to come up with the best way to add the total time an employee was clocked in.

The punch types are day in, break out, break in & day out. You would think I could just select * where date = DATE, but that doesn't work if the employee arrived at 11:30pm and left at 2:00am the following day.

I was thinking something like this might be the best solution, but I don't know if it's practical or even possible:

Select * from the LAST RECORD where employee = employee STOP at the first instance of "clock in".

This would gather all the punches since the employee last clocked in. For example:

ID NAME TYPE TIME

45 John Day In TIME

46 Joe Day In TIME

47 Mary Break Out TIME

48 Joe Break Out TIME

49 Joe Break In TIME

50 John Day Out TIME

51 Mary Break In TIME

52 Joe Day Out TIME

53 Mary Day Out TIME

So in the example I was thinking about, if you're looking for Joe's time, it would start searching from punch id 53 backwards until it reached punch 46. The result would be punches 46, 48, 49 and 51. I could then calculate the total time worked. Again, I don't know if this is even possible.

I would REALLY appreciate any comments/suggestions in regards to how to accomplish this, or any other ways that might be more practical to do this!

Answer 1

I would use query like the next:

select workerid, sum(case 
when punchtype = 'in' then -DATEDIFF(MINUTE, '20110901', punchtime)
when punchtype = 'out' then DATEDIFF(MINUTE, '20110901', punchtime)
when punchtype = 'bin' then -DATEDIFF(MINUTE, '20110901', punchtime)
when punchtype = 'bout' then DATEDIFF(MINUTE, '20110901', punchtime)
end )/60 as WorkingHours
from Punchlog
group by workerid

Remarks: Lets assume I have table:

create table PunchLog
(workerid int not null,
punchtime datetime not null,
punchtype char(4) not null)

with punch data from few days.

Lets put some test data:

insert into PunchLog (workerid, punchtime, punchtype) values (1, '20110901 23:30', 'in')
insert into PunchLog (workerid, punchtime, punchtype) values (1, '20110902 03:30', 'out')
insert into PunchLog (workerid, punchtime, punchtype) values (1, '20110902 00:15', 'bin')
insert into PunchLog (workerid, punchtime, punchtype) values (1, '20110902 01:15', 'bout')
insert into PunchLog (workerid, punchtime, punchtype) values (2, '20110901 09:30', 'in')
insert into PunchLog (workerid, punchtime, punchtype) values (2, '20110901 17:00', 'out')
insert into PunchLog (workerid, punchtime, punchtype) values (2, '20110901 14:30', 'bin')
insert into PunchLog (workerid, punchtime, punchtype) values (2, '20110901 10:00', 'bout')

Time person spent at working place is:

T = out - in - (bin -bout) = out - in -bin + bout

where in, out is day punch time and bin, bout is break punch time.

If I will group by workerid and apply sum() to punchtime then I will have working time BUT I need in and bin time were negative. Also I cannot sum datetime. Thus I will change punchtime to span from any date in the past and use case statement to set sign:

sum(case 
when punchtype = 'in' then -DATEDIFF(MINUTE, '20110901', punchtime)
when punchtype = 'out' then DATEDIFF(MINUTE, '20110901', punchtime)
. . .
end )/60 as WorkingHours

in my test it produces result:

workerid  WorkingHours
--------  ------------
1         5
2         3

Answer 2

You already have the employee Id, the punch type Id, but you are missing the day or period Id, which will make things a lot easier:

PunchId  PeriodId  Employee  PunchTypeId  PunchDateTime
-------- --------- --------- ------------ --------------
...
44       1         Mary      Day In       TIME
45       1         John      Day In       TIME
46       1         Joe       Day In       TIME
47       1         Mary      Break Out    TIME
48       1         Joe       Break Out    TIME
49       1         Joe       Break In     TIME
50       1         John      Day Out      TIME
51       1         Mary      Break In     TIME
52       1         Joe       Day Out      TIME
53       1         Mary      Day Out      TIME
54       2         John      Day In       TIME
55       2         Mary      Day In       TIME
56       2         Joe       Day In       TIME
...

Then you can use something like:

select di.Employee, di.PeriodId, 
    timediff(do.DayOutTime, bi.BreakInTime)
    + timediff(bo.BreakOutTime, di.DayInTime) as PeriodWorkedHours
from
(
    select Employee, PeriodId, PunchDateTime as DayInTime
    from punches
    where PunchTypeId = 'Day In'
) as di
inner join (
    select Employee, PeriodId, PunchDateTime BreakOutTime
    from punches
    where PunchTypeId = 'Break Out'
) as bo on di.Employee = bo.Employee and di.PeriodId = bo.PeriodId
inner join (
    select Employee, PeriodId, PunchDateTime as BreakInTime
    from punches
    where PunchTypeId = 'Break In'
) as bi on di.Employee = bi.Employee and di.PeriodId = bi.PeriodId
inner join (
    select Employee, PeriodId, PunchDateTime as DayOutTime
    from punches
    where PunchTypeId = 'Day Out'
) as do on di.Employee = do.Employee and di.PeriodId = do.PeriodId

That is assuming that within a PeriodId all employees have four punches each (Day In, Break Out, Break In, and Day Out). Also, the PeriodId should not be tied to the date to handle the case you mention in your question, when punch times cross day boundaries. Rather, increment the PeriodId for an employee after inserting the last punch for the period (i.e. Day Out punch).

Answer 3

Breaking the problem down into steps, firstly list all 'IN' records:

SELECT p_in.*
FROM punches p_in
WHERE p_in.type = 'IN'

Next, write a subquery to find the next 'OUT' record (this method assumes id is auto-incremented for simplicity):

        SELECT MIN(pa.id)
        FROM punches pa
        WHERE pa.type = 'OUT'
        AND pa.name = p_in.name
        AND pa.time > p_in.time

Now, wrap this query in an outer select that looks like the punches table:

    SELECT pb.id, pb.name, pb.type, pb.time
    FROM punches pb
    WHERE pb.id = (
        SELECT MIN(pa.id)
        FROM punches pa
        WHERE pa.type = 'OUT'
        AND pa.name = p_in.name
        AND pa.time > p_in.time
    )

And use this in a LEFT JOIN to the original query:

SELECT
    p_in.id     in_id,
    p_in.name   in_name,
    p_in.type   in_type,
    p_in.time   in_time,
    p_out.id    out_id,
    p_out.name  out_name,
    p_out.type  out_type,
    p_out.time  out_time
FROM punches p_in
LEFT JOIN (
    SELECT pb.id, pb.name, pb.type, pb.time
    FROM punches pb
    WHERE pb.id = (
        SELECT MIN(pa.id)
        FROM punches pa
        WHERE pa.type = 'OUT'
        AND pa.name = p_in.name
        AND pa.time > p_in.time
    )
) p_out
WHERE p_in.type = 'IN'

This will give you a result set where each 'in' record has the next corresponding 'out' record. If there is no corresponding out record, the out values will be set to null.

Hopefully you can use these result to calculate what you need.