By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48018 in the last week).
select version();
version()
10.5.12-MariaDB
…
hidden batch(es)
CREATE TABLE vacaciones (
no_empleado INT,
inicial DATE,
final DATE);
INSERT INTO vacaciones VALUES
(1001,'2020-12-24','2021-01-04');
✓
✓
hidden batch(es)
CREATE TABLE festivos (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fecha DATE,
descripcion VARCHAR(255));
INSERT INTO festivos(fecha, descripcion) VALUES
('2020-12-25','Navidad'),
('2021-01-01','Año Nuevo');
✓
✓
hidden batch(es)
SELECT no_empleado,
EXTRACT(YEAR FROM t.Date) AS ano,
EXTRACT(MONTH FROM t.Date) AS mes,
SUM(CASE WHEN f.fecha IS NULL THEN WEEKDAY(`Date`) < 5 END) AS dias
FROM (SELECT v.no_empleado, DATE_ADD(v.inicial, interval s.seq - 1 DAY) AS Date
FROM vacaciones v CROSS JOIN seq_1_to_100 s
WHERE DATE_ADD(v.inicial, interval s.seq - 1 DAY) <= v.final
ORDER BY v.no_empleado, v.inicial, s.seq ) t
LEFT JOIN festivos f ON t.Date=f.fecha
GROUP BY no_empleado, EXTRACT(YEAR_MONTH FROM t.Date);
