clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798840 fiddles created (41827 in the last week).

CREATE TABLE `events` ( `ID` int(10) UNSIGNED NOT NULL, `evt_id` varchar(10) NOT NULL, `service` int(1) DEFAULT NULL, `start` varchar(255) NOT NULL, `end` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `events` -- INSERT INTO `events` (`ID`, `evt_id`, `service`, `start`, `end`) VALUES (464, '5423', 2, '2021-06-21 18:00:00', '2021-06-21 19:00:00'), (465, '5424', 3, '2021-06-22 18:00:00', '2021-06-22 19:00:00'), (474, '5436', 0, '2021-06-19 09:00:00', '2021-06-19 10:00:00'), (475, '5437', 0, '2021-06-23 18:00:00', '2021-06-23 21:00:00'), (476, '5438', 0, '2021-06-24 16:00:00', '2021-06-24 17:00:00'), (477, '5439', 2, '2021-06-21 16:00:00', '2021-06-21 17:00:00'), (478, '5441', 2, '2021-06-21 17:00:00', '2021-06-21 18:00:00'), (479, '5442', 1, '2021-06-30 17:00:00', '2021-06-30 18:00:00'), (480, '5443', 1, '2021-06-28 17:00:00', '2021-06-28 18:00:00'), (484, '5447', 1, '2021-06-23 17:00:00', '2021-06-23 18:00:00'), (486, '5449', 2, '2021-06-22 17:00:00', '2021-06-22 18:00:00'), (491, '5450', 2, '2021-06-23 16:00:00', '2021-06-23 17:00:00'), (494, '5455', 1, '2021-06-25 15:00:00', '2021-06-25 16:00:00'), (495, '5456', 2, '2021-06-25 16:00:00', '2021-06-25 17:00:00'), (496, '5457', 1, '2021-06-25 17:00:00', '2021-06-25 18:00:00'), (497, '5458', 3, '2021-06-25 18:00:00', '2021-06-25 19:00:00'), (498, '5459', 2, '2021-06-25 14:00:00', '2021-06-25 15:00:00'); -- -- Indizes der exportierten Tabellen -- -- -- Indizes für die Tabelle `events2` -- ALTER TABLE `events` ADD PRIMARY KEY (`ID`); -- -- AUTO_INCREMENT für exportierte Tabellen -- -- -- AUTO_INCREMENT für Tabelle `events` -- ALTER TABLE `events` MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=381; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 hidden batch(es)


SELECT t.timeslots AS timeslots, IF(COUNT(e_serviceA.id) > 0, 'blocked', null) AS service13, IF(COUNT(e_serviceB.id) > 0, 'blocked', null) AS service2 FROM ( SELECT DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as timeslots FROM seq_0_to_30 AS day, seq_14_to_18 AS hour ) t LEFT JOIN events e_serviceA ON t.timeslots BETWEEN DATE_SUB(e_serviceA.start, INTERVAL 4 HOUR) AND DATE_ADD(e_serviceA.start, INTERVAL 59 MINUTE) AND e_serviceA.service=2 LEFT JOIN events e_serviceB ON t.timeslots BETWEEN e_serviceB.start AND DATE_ADD(e_serviceB.start, INTERVAL 239 MINUTE) AND e_serviceB.service=2 GROUP BY t.timeslots HAVING `service2` IS NULL AND timeslots NOT IN ( SELECT `start` FROM `events`) ORDER BY t.timeslots
timeslots service13 service2
2021-06-20 14:00:00
2021-06-20 15:00:00
2021-06-20 16:00:00
2021-06-20 17:00:00
2021-06-20 18:00:00
2021-06-21 14:00:00 blocked
2021-06-21 15:00:00 blocked
2021-06-22 14:00:00 blocked
2021-06-22 15:00:00 blocked
2021-06-22 16:00:00 blocked
2021-06-23 14:00:00 blocked
2021-06-23 15:00:00 blocked
2021-06-24 14:00:00
2021-06-24 15:00:00
2021-06-24 17:00:00
2021-06-24 18:00:00
2021-06-26 14:00:00
2021-06-26 15:00:00
2021-06-26 16:00:00
2021-06-26 17:00:00
2021-06-26 18:00:00
2021-06-27 14:00:00
2021-06-27 15:00:00
2021-06-27 16:00:00
2021-06-27 17:00:00
2021-06-27 18:00:00
2021-06-28 14:00:00
2021-06-28 15:00:00
2021-06-28 16:00:00
2021-06-28 18:00:00
2021-06-29 14:00:00
2021-06-29 15:00:00
2021-06-29 16:00:00
2021-06-29 17:00:00
2021-06-29 18:00:00
2021-06-30 14:00:00
2021-06-30 15:00:00
2021-06-30 16:00:00
2021-06-30 18:00:00
2021-07-01 14:00:00
2021-07-01 15:00:00
2021-07-01 16:00:00
2021-07-01 17:00:00
2021-07-01 18:00:00
2021-07-02 14:00:00
2021-07-02 15:00:00
2021-07-02 16:00:00
2021-07-02 17:00:00
2021-07-02 18:00:00
2021-07-03 14:00:00
2021-07-03 15:00:00
2021-07-03 16:00:00
2021-07-03 17:00:00
2021-07-03 18:00:00
2021-07-04 14:00:00
2021-07-04 15:00:00
2021-07-04 16:00:00
2021-07-04 17:00:00
2021-07-04 18:00:00
2021-07-05 14:00:00
2021-07-05 15:00:00
2021-07-05 16:00:00
2021-07-05 17:00:00
2021-07-05 18:00:00
2021-07-06 14:00:00
2021-07-06 15:00:00
2021-07-06 16:00:00
2021-07-06 17:00:00
2021-07-06 18:00:00
2021-07-07 14:00:00
2021-07-07 15:00:00
2021-07-07 16:00:00
2021-07-07 17:00:00
2021-07-07 18:00:00
2021-07-08 14:00:00
2021-07-08 15:00:00
2021-07-08 16:00:00
2021-07-08 17:00:00
2021-07-08 18:00:00
2021-07-09 14:00:00
2021-07-09 15:00:00
2021-07-09 16:00:00
2021-07-09 17:00:00
2021-07-09 18:00:00
2021-07-10 14:00:00
2021-07-10 15:00:00
2021-07-10 16:00:00
2021-07-10 17:00:00
2021-07-10 18:00:00
2021-07-11 14:00:00
2021-07-11 15:00:00
2021-07-11 16:00:00
2021-07-11 17:00:00
2021-07-11 18:00:00
2021-07-12 14:00:00
2021-07-12 15:00:00
2021-07-12 16:00:00
2021-07-12 17:00:00
2021-07-12 18:00:00
2021-07-13 14:00:00
2021-07-13 15:00:00
2021-07-13 16:00:00
2021-07-13 17:00:00
2021-07-13 18:00:00
2021-07-14 14:00:00
2021-07-14 15:00:00
2021-07-14 16:00:00
2021-07-14 17:00:00
2021-07-14 18:00:00
2021-07-15 14:00:00
2021-07-15 15:00:00
2021-07-15 16:00:00
2021-07-15 17:00:00
2021-07-15 18:00:00
2021-07-16 14:00:00
2021-07-16 15:00:00
2021-07-16 16:00:00
2021-07-16 17:00:00
2021-07-16 18:00:00
2021-07-17 14:00:00
2021-07-17 15:00:00
2021-07-17 16:00:00
2021-07-17 17:00:00
2021-07-17 18:00:00
2021-07-18 14:00:00
2021-07-18 15:00:00
2021-07-18 16:00:00
2021-07-18 17:00:00
2021-07-18 18:00:00
2021-07-19 14:00:00
2021-07-19 15:00:00
2021-07-19 16:00:00
2021-07-19 17:00:00
2021-07-19 18:00:00
2021-07-20 14:00:00
2021-07-20 15:00:00
2021-07-20 16:00:00
2021-07-20 17:00:00
2021-07-20 18:00:00
 hidden batch(es)