CREATE DATABASE test;
use test;
CREATE TABLE holidays (
id int,
holiday_date DATE
);
INSERT INTO holidays VALUES (1, '2024-08-08');
SELECT * FROM holidays;
CREATE TABLE time_periods (
id int,
startdate DATE,
enddate DATE
);
INSERT INTO time_periods VALUES (1, '2024-08-01', '2024-08-10');
INSERT INTO time_periods VALUES (2, '2024-08-11', '2024-08-20');
SELECT * FROM time_periods;
SELECT tp.id, COUNT(*) AS workdays
FROM time_periods tp
JOIN
(
SELECT 0 AS n
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) numbers
ON DATE_ADD(tp.startdate, INTERVAL numbers.n DAY) <= tp.enddate
WHERE
DATE_ADD(tp.startdate, INTERVAL numbers.n DAY) NOT IN (SELECT holiday_date FROM holidays)
GROUP BY
tp.id;