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 */;

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