clear markdown compare help best fiddles feedback
clear markdown feedback
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);
no_empleado ano mes dias
1001 2020 12 5
1001 2021 1 1
 hidden batch(es)