How to track if employees are late which has more than one work schedules or shifts?

Question

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?

addendum:

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?

Answer 1

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:

(pseudocode)

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.

Answer 2

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.