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 52. I could then calculate the total time worked. Again, I don't know if this is even possible. Another curve ball is that an employee may or may not take a break, and may even take more than one break in a day. Or maybe I could use LEFT JOIN somehow to give 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. 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! I can work with PHP all day, but I get sort of lost at the more complex mysql queries.
This is going to be tricky. The problem is that there is no reference to a single day for an employee. You need to recursively find both the in and out times for the same day in difference records. You need several queries and probably some application logic to get the together. The first should put the time in and out into a single result set. You will need another to pull all the break time. You would finally need a 3rd to add the results from the queries to get the correct time. If possible I would strongly suggest changing your schema and using a single record with an in and out on the same row. This could be either clocking in/out break in/out, but by having in and out on the same row, you can pull all the data in a single query. Something like: ID NAME TYPE TIME_IN TIME_OUT Would really simplify retrieving data.
Thanks for your comments. If I set the database up that way, how should I do the queries? The most trouble I'm having is joining the two queries together.
You select for all records for the employee where the in time is the date in question. If the out time is earlier than the in time, add 24 to the hour of the out time. Then calculate the duration of each record. Then add all the calculations (if there are more than one).