I have a query that includes two WITH clauses. The first one, ‘feriados’, returns every day between the start date and end date from a row in a table named ‘holidays’. A holiday may start and end on the same day or have different start and end dates. The second clause defines a function named ‘data_distinta’, which determines whether a day is a valid work day or not. A valid work day is defined as a day that is neither a weekend nor a holiday (being a holiday is filtered by the ‘feriados’ function). When it’s a valid work day, the function returns 1; otherwise, it returns 0.

The issue I’m encountering is that the final result is showing two identical days with opposite values for ‘valid work day’.

I’m unable to create a new table to represent the calendar, and the existing calendar table only includes holidays.

WITH RECURSIVE feriados AS 
(
    SELECT begin_date AS dateValue, entities_id AS id
    FROM  holidays
    UNION ALL
    SELECT DATE_ADD(dateValue, INTERVAL 1 DAY), id AS id
    FROM feriados
    WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= (SELECT end_date FROM  holidays WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) BETWEEN begin_date AND end_date)
),

data_distinta AS 
(
    SELECT DISTINCT DATE( tickets.date) AS data, 
    
    (CASE
            WHEN WEEKDAY( tickets.date)>=5 THEN 0
            WHEN WEEKDAY( tickets.date)<5 AND DATE( tickets.date) NOT LIKE feriados.dateValue THEN 1
            WHEN DATE( tickets.date) LIKE feriados.dateValue THEN 0 

    END
    ) AS validWorkDay
    
    FROM  tickets
    LEFT JOIN  entities
        ON ( tickets.entities_id =  entities.id)
    LEFT JOIN  calendars
        ON ( calendars.entities_id =  entities.id)
    LEFT JOIN  calendars_holidays
        ON ( calendars.id =  calendars_holidays.calendars_id)
    LEFT JOIN  holidays
        ON ( holidays.id =  calendars_holidays.holidays_id)
    JOIN feriados)
SELECT data_distinta.data, data_distinta.validWorkDay
FROM data_distinta
WHERE data_distinta.data >= '05/01/2024'

this is the code I have

Im expecting

Expected result

but im getting
Actual result