I have designed a MySQL database for an employee monitoring system, it was working perfectly well until I was asked to put a new work schedule for some of the employees.

The new schedule was what they called a broken schedule because an employee would have different shifts in one day, for example, an employee could be given a work schedule for a given day starting from 7:00AM-11:00AM and then continues at 3:00PM-7:00PM...

The problem I encountered now is, I am confused on how to keep track if the employees are late of not now that some employees have two or more work schedules...

Could anyone help me pls?


for more clarity, lets say we have employee A. employee A for example has the following work schedule for a given day - 5 days in a week.
7:00AM-11:00AM - shift 1
3:00PM-7:00PM - shift 2
9:00PM-11:30PM - shift 3

then, lets say employee A logged in at these different scenarios:
scenario #1: logged in at 7:10AM
scenario #2: logged in at 2:30PM

how do I check that employee A's log info at scenario #1 is not referencing shift2 or shift3?
likewise, how do I check if employee A's log info at scenario #2 would only reference shift2 only and would not make him/her late at his/her schedule at shift #1 or too early at shift #3?

2 Answers 2


The rules I would have applied to determine the validity of the scenarios seems pretty straightforward:

When a clock-in occurs between the start and end times of "any" assigned shift, that's a late clock-in for the shift with the start and end times that the clock-in occurs "between" (scenario 1)...

Conversely, when the clock-in is prior to the start time of an assigned shift but also after the end time of another shift (or, to state this another way, it occurs "not between" the start and end of any other assigned shift), that's an early clock-in (scenario 2) for the next shift to start after the clock-in event.

It seems that both pieces can be combined together:


SELECT sch.start_time, sch.stop_time from employee JOIN employee_schedule JOIN schedule sch WHERE sch.stop_time > clock_in_time ORDER BY stop_time LIMIT 1;

I would suggest that this query should always return the schedule to which the clock-in timestamp applies. It's "the first shift that ended after you clocked in."

If the start_time of the shift is earlier than the clock_in_time, then you're late... if it's later than the clock_in_time, then you're early.

  • awesome!!! works perfect, thank you so much sir
    – chad
    Sep 5, 2013 at 2:33

If you absolutely cannot add a column to your schedule or employee to reference the shift in question explicitly, you could make assumptions regarding the length of time between a log in and a shift start to determine this.

  • You mean linking them together?...My current table relationship for employee and their schedule uses 3 tables, the 'employee' table containing employee info, the 'schedule' table containing schedule info, and a junction table named 'employee_schedule' for linking the employee and a schedule. inside the emplyee_schedule table, an employee could be linked into several schedules or shifts...
    – chad
    Sep 4, 2013 at 22:34
  • Also, I already tried doing your second suggestion by making assumptions regarding the length of time between a log in and a shift start...and it kinda brings me back to my previous problem like in my posted question above...
    – chad
    Sep 4, 2013 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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