Get a work schedule template based on date range

I have a table in my MariaDB 10.3 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 for employee 476:

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:

WITH RECURSIVE date_range (`date`) AS (
    SELECT '2023-09-25' -- start date
    UNION ALL
    SELECT `date` + INTERVAL 1 DAY FROM date_range WHERE `date` < '2023-10-01' -- end date
),
ehp AS (
    SELECT *, LEAD(validez) OVER (PARTITION BY diasem ORDER BY validez) - INTERVAL 1 DAY AS validez_fin
    FROM empleados_horarios_predeterminados
    WHERE id_pers_empleado = 5
)
SELECT 
  ehp.id_pers_empleado employee_id,
  dr.`date`,
  ehp.hora_inicio `from`,
  ehp.hora_fin `to`,
  ehp.validez `starts`,
  ehp.validez_fin `ends`,
  (TIME_TO_SEC(ehp.hora_fin) - TIME_TO_SEC(ehp.hora_inicio)) / 3600 AS worked
FROM date_range dr
JOIN ehp ON WEEKDAY(dr.`date`) + 1 = ehp.diasem
  AND ehp.validez <= dr.`date`
  AND (ehp.validez_fin >= dr.`date` OR ehp.validez_fin IS NULL);

Which gave me the correct resultset for the specific case I've been talking, but I'm encountering problems with other users with older schedule templates or multiple shifts in same day.

When I'm querying for employee 5 (an old one) in date range between "2023-09-25" and "2023-10-01", it's resulting in the following recordset:

First problem: it's not showing the shift from 07:30 to 15:00 for weekday 4.

Second problem: it's showing a record for weekday 6 from a very old schedule template, long time ago deprecated by newer ones.

Third problem: it's not calculating correctly shifts ending after 23:59, resulting in negative working hours.

To correctly handle the three problems stated in your update, you can add a CTE (schedules below) to identify the validez dates of the shift templates applicable for the employee and date range required:

SET @start_date = '2023-09-25', @end_date = '2023-10-01', @employee = 5;

WITH RECURSIVE date_range (dt) AS (
    SELECT @start_date
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_range WHERE dt < @end_date
),
schedules (validez, validez_fin) AS (
    SELECT validez, COALESCE(LEAD(validez) OVER (ORDER BY validez) - INTERVAL 1 DAY, @end_date)
    FROM empleados_horarios_predeterminados
    WHERE validez BETWEEN (SELECT MAX(validez) FROM empleados_horarios_predeterminados WHERE id_pers_empleado = @employee AND validez <= @start_date)
                      AND (SELECT MAX(validez) FROM empleados_horarios_predeterminados WHERE id_pers_empleado = @employee AND validez <= @end_date)
    AND id_pers_empleado = @employee
    GROUP BY validez
)
SELECT
    dr.dt,
    ehp.*,
    (TIME_TO_SEC(IF(ehp.hora_fin < ehp.hora_inicio, ehp.hora_fin + INTERVAL 24 HOUR, ehp.hora_fin)) - TIME_TO_SEC(ehp.hora_inicio)) / 3600 AS worked
FROM date_range dr
JOIN schedules s
    ON dr.dt BETWEEN s.validez AND s.validez_fin
JOIN empleados_horarios_predeterminados ehp
    ON ehp.id_pers_empleado = @employee
    AND s.validez = ehp.validez
    AND WEEKDAY(dr.dt) + 1 = ehp.diasem
ORDER BY dr.dt, ehp.hora_inicio;

Here's another db<>fiddle.

Or you could add a correlated subquery to the result of the date_range CTE:

SET @start_date = '2023-09-25', @end_date = '2023-10-01', @employee = 5;

WITH RECURSIVE date_range (dt) AS (
    SELECT @start_date
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_range WHERE dt < @end_date
),
dr_with_validez AS (
    SELECT *,
        (
            SELECT MAX(validez)
            FROM empleados_horarios_predeterminados
            WHERE id_pers_empleado = @employee
            AND validez <= dr.dt
        ) AS validez
    FROM date_range dr
)
SELECT
    dr.dt,
    ehp.*,
    (TIME_TO_SEC(IF(ehp.hora_fin < ehp.hora_inicio, ehp.hora_fin + INTERVAL 24 HOUR, ehp.hora_fin)) - TIME_TO_SEC(ehp.hora_inicio)) / 3600 AS worked
FROM dr_with_validez dr
JOIN empleados_horarios_predeterminados ehp
    ON ehp.id_pers_empleado = @employee
    AND dr.validez = ehp.validez
    AND WEEKDAY(dr.dt) + 1 = ehp.diasem
ORDER BY dr.dt, ehp.hora_inicio;

db<>fiddle

We don't know all your use cases but you might want to consider splitting the current table to separate the shift template and the days/times of the individual shifts:

shift_templates (id, id_pers_empleado, validez)
shift_template_shifts (shift_template_id, diasem, hora_inicio, hora_fin)