I have a table in my MySQL database with schedule templates for my workers, which are then used in a schedule planner. When there is a modification to the employee’s employment contract, a new set of records is added with the new schedule.

So far I have the following query:

SELECT
    ehp.id_pers_empleado employee_id,
    ehp.diasem weekday,
    ehp.hora_inicio `from`,
    ehp.hora_fin `to`,
    ehp.validez `starts`,
    TIMESTAMPDIFF( MINUTE, ehp.hora_inicio, ehp.hora_fin ) / 60 worked
FROM
    empleados_horarios_predeterminados ehp 
WHERE
    id_pers_empleado = 476 
ORDER BY
    `starts`,
    weekday 

That query returns the following fields:

In this way, the records I obtain are the following:

I need to know what work schedule the employee had in a specific date range.

For example, if I query the week from 4/29/2024 to 5/5/2024, I would expect to receive:

• Monday 04/29 → Day of week 1 of the schedule that began on 04/01/2024
• Tuesday 04/30 → Day of week 2 of the schedule that began on 04/01/2024
• Wednesday 05/01 → Day of week 3 of the schedule that began on 05/01/2024
• Thursday 05/02 → Day of week 4 of the schedule that began on 05/01/2024
• Friday 05/03 → Day of week 5 of the schedule that began on 05/01/2024

That is, the following set of records:

With this data I want to know how many hours that employee had worked that week, or automatically create the schedule for future weeks.

I’ve been racking my brain all day trying to find a way to make it possible, but everything I try doesn’t give the expected result.

Last try was:

SELECT
    * 
FROM (
    SELECT
        ehp.id_pers_empleado AS employee_id,
        ehp.diasem AS weekday,
        ehp.hora_inicio AS `from`,
        ehp.hora_fin AS `to`,
        ehp.validez AS `starts`,
        TIMESTAMPDIFF( MINUTE, ehp.hora_inicio, ehp.hora_fin ) / 60 AS worked,
        DATE_ADD( "2024-04-29", INTERVAL diasem - 1 DAY ) effective_day 
    FROM
        empleados_horarios_predeterminados ehp 
    WHERE
        id_pers_empleado = 476 
    ORDER BY
        STARTS,
        weekday 
) q1 
WHERE
    effective_day >= q1.`starts` 
    AND STARTS <= "2024-05-01" 
GROUP BY
    weekday

Which gave me the correct resultset for the specific case I’ve been talking, but it is failing with other employees having more than 2 schedules or in other date ranges.