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

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!

This question and answers originated from www.stackoverflow.com
Question by (10/7/2011 11:19:21 PM)

Answer

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.

Answer by
Find More Answers
Related Topics  mysql  query  math  time
Related Questions