/* <-- zurück zu Zeitintervalle in SQL verarbeiten */ /* SQL Server: Testdaten einfügen */ TRUNCATE TABLE zeitintervall_testdaten; INSERT INTO zeitintervall_testdaten (fallnr, beginn, ende) SELECT fallnr, CONVERT(DATETIME, beginn, 120), CONVERT(DATETIME, ende, 120) FROM ( SELECT 1 AS fallnr,'2007-08-03 13:38:00' AS beginn, '2007-08-04 12:25:00' AS ende UNION SELECT 1 AS fallnr,'2007-08-05 19:28:00' AS beginn, '2007-08-05 23:08:00' AS ende UNION SELECT 2 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-23 10:47:00' AS ende UNION SELECT 3 AS fallnr,'2007-08-06 00:21:00' AS beginn, '2007-08-06 08:37:00' AS ende UNION SELECT 3 AS fallnr,'2007-08-06 14:37:00' AS beginn, '2007-08-06 15:15:00' AS ende UNION SELECT 3 AS fallnr,'2007-08-10 15:41:00' AS beginn, '2007-08-10 17:34:00' AS ende UNION SELECT 3 AS fallnr,'2007-08-14 13:35:00' AS beginn, '2007-08-14 14:33:00' AS ende UNION SELECT 3 AS fallnr,'2007-08-17 22:08:00' AS beginn, '2007-08-18 01:20:00' AS ende UNION SELECT 4 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-05 14:32:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-10 13:39:00' AS beginn, '2007-08-10 20:06:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-10 18:19:00' AS beginn, '2007-08-11 08:13:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-11 19:19:00' AS beginn, '2007-08-12 05:28:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-17 12:27:00' AS beginn, '2007-08-17 12:56:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-17 13:32:00' AS beginn, '2007-08-25 21:55:00' AS ende UNION SELECT 5 AS fallnr,'2007-08-16 20:39:00' AS beginn, '2007-08-17 12:27:00' AS ende UNION SELECT 6 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-05 04:09:00' AS ende UNION SELECT 7 AS fallnr,'2007-08-13 15:15:00' AS beginn, '2007-08-14 20:04:00' AS ende UNION SELECT 8 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 9 AS fallnr,'2007-08-12 17:22:00' AS beginn, '2007-08-12 17:45:00' AS ende UNION SELECT 10 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-08 09:26:00' AS ende UNION SELECT 11 AS fallnr,'2007-08-11 13:52:00' AS beginn, '2007-08-12 03:01:00' AS ende UNION SELECT 12 AS fallnr,'2007-08-05 19:12:00' AS beginn, '2007-08-06 10:30:00' AS ende UNION SELECT 13 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-03 13:00:00' AS ende UNION SELECT 13 AS fallnr,'2007-08-03 13:00:00' AS beginn, '2007-08-09 15:47:00' AS ende UNION SELECT 14 AS fallnr,'2007-08-19 19:35:00' AS beginn, '2007-08-20 05:21:00' AS ende UNION SELECT 15 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-10 11:50:00' AS ende UNION SELECT 16 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-04 07:25:00' AS ende UNION SELECT 16 AS fallnr,'2007-08-04 19:04:00' AS beginn, '2007-08-09 10:24:00' AS ende UNION SELECT 17 AS fallnr,'2007-08-01 15:52:00' AS beginn, '2007-08-01 17:34:00' AS ende UNION SELECT 17 AS fallnr,'2007-08-01 19:52:00' AS beginn, '2007-08-01 21:17:00' AS ende UNION SELECT 17 AS fallnr,'2007-08-02 08:29:00' AS beginn, '2007-08-02 09:15:00' AS ende UNION SELECT 17 AS fallnr,'2007-08-02 17:38:00' AS beginn, '2007-08-02 18:14:00' AS ende UNION SELECT 17 AS fallnr,'2007-08-02 21:07:00' AS beginn, '2007-08-02 21:37:00' AS ende UNION SELECT 18 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-05 11:39:00' AS ende UNION SELECT 18 AS fallnr,'2007-08-05 11:50:00' AS beginn, '2007-08-11 11:54:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-04 08:00:00' AS beginn, '2007-08-09 16:18:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-11 13:15:00' AS beginn, '2007-08-13 12:58:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-15 10:08:00' AS beginn, '2007-08-15 10:16:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-15 11:02:00' AS beginn, '2007-08-15 11:08:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-15 14:04:00' AS beginn, '2007-08-15 14:09:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-15 17:13:00' AS beginn, '2007-08-15 17:14:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-16 04:47:00' AS beginn, '2007-08-16 04:52:00' AS ende UNION SELECT 19 AS fallnr,'2007-08-16 13:21:00' AS beginn, '2007-08-16 13:31:00' AS ende UNION SELECT 20 AS fallnr,'2007-08-09 13:08:00' AS beginn, '2007-08-14 14:50:00' AS ende UNION SELECT 20 AS fallnr,'2007-08-14 14:51:00' AS beginn, '2007-08-19 12:25:00' AS ende UNION SELECT 20 AS fallnr,'2007-08-19 12:25:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-01 00:00:00' AS beginn, '2007-08-02 13:00:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-03 10:57:00' AS beginn, '2007-08-03 12:53:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-03 17:54:00' AS beginn, '2007-08-03 18:59:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-03 20:58:00' AS beginn, '2007-08-03 21:32:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-04 00:54:00' AS beginn, '2007-08-04 03:01:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-04 10:01:00' AS beginn, '2007-08-04 11:13:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-04 11:13:00' AS beginn, '2007-08-06 13:10:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-07 07:44:00' AS beginn, '2007-08-07 12:26:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-07 15:13:00' AS beginn, '2007-08-07 16:31:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-08 08:16:00' AS beginn, '2007-08-08 11:57:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-08 15:58:00' AS beginn, '2007-08-08 17:29:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-09 11:23:00' AS beginn, '2007-08-09 12:16:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-09 18:19:00' AS beginn, '2007-08-09 18:59:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-10 10:08:00' AS beginn, '2007-08-10 10:45:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-10 14:48:00' AS beginn, '2007-08-10 16:10:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-11 08:06:00' AS beginn, '2007-08-11 09:10:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-11 19:26:00' AS beginn, '2007-08-11 20:20:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-12 07:38:00' AS beginn, '2007-08-12 09:21:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-12 17:56:00' AS beginn, '2007-08-12 18:34:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-12 22:20:00' AS beginn, '2007-08-12 23:36:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-13 08:05:00' AS beginn, '2007-08-13 08:56:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-13 12:05:00' AS beginn, '2007-08-14 03:14:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-15 08:54:00' AS beginn, '2007-08-15 11:42:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-16 04:31:00' AS beginn, '2007-08-16 07:35:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-16 07:31:00' AS beginn, '2007-08-16 14:05:00' AS ende UNION SELECT 43 AS fallnr,'2007-08-16 14:05:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 46 AS fallnr,'2007-08-15 17:30:00' AS beginn, '2007-08-18 20:35:00' AS ende UNION SELECT 46 AS fallnr,'2007-08-18 20:30:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-12 16:32:00' AS beginn, '2007-08-15 10:30:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-16 11:46:00' AS beginn, '2007-08-16 16:00:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-16 16:00:00' AS beginn, '2007-08-17 00:15:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-17 03:00:00' AS beginn, '2007-08-17 07:20:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-17 07:25:00' AS beginn, '2007-08-18 02:58:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-18 02:57:00' AS beginn, '2007-08-18 08:05:00' AS ende UNION SELECT 96 AS fallnr,'2007-08-18 08:05:00' AS beginn, '2007-08-22 09:00:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-13 13:36:00' AS beginn, '2007-08-13 18:00:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-13 18:00:00' AS beginn, '2007-08-14 02:14:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-17 21:51:00' AS beginn, '2007-08-18 04:48:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-19 04:15:00' AS beginn, '2007-08-19 09:33:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-19 14:28:00' AS beginn, '2007-08-19 16:50:00' AS ende UNION SELECT 126 AS fallnr,'2007-08-19 16:45:00' AS beginn, '2007-08-22 00:25:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-10 23:39:00' AS beginn, '2007-08-11 04:10:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-12 01:59:00' AS beginn, '2007-08-12 02:15:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-12 05:10:00' AS beginn, '2007-08-12 12:26:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-12 12:26:00' AS beginn, '2007-08-12 13:02:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-12 13:02:00' AS beginn, '2007-08-13 12:26:00' AS ende UNION SELECT 138 AS fallnr,'2007-08-13 10:51:00' AS beginn, '2007-08-13 12:28:00' AS ende UNION SELECT 152 AS fallnr,'2007-08-11 14:09:00' AS beginn, '2007-08-13 10:30:00' AS ende UNION SELECT 152 AS fallnr,'2007-08-13 19:03:00' AS beginn, '2007-08-13 21:42:00' AS ende UNION SELECT 152 AS fallnr,'2007-08-13 21:37:00' AS beginn, '2007-08-18 09:35:00' AS ende UNION SELECT 152 AS fallnr,'2007-08-18 09:35:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-12 15:40:00' AS beginn, '2007-08-12 19:23:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-16 13:50:00' AS beginn, '2007-08-16 14:24:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-16 23:46:00' AS beginn, '2007-08-17 02:09:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-17 03:42:00' AS beginn, '2007-08-17 08:09:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-17 08:09:00' AS beginn, '2007-08-17 13:09:00' AS ende UNION SELECT 179 AS fallnr,'2007-08-17 13:08:00' AS beginn, '2007-08-27 21:25:00' AS ende UNION SELECT 220 AS fallnr,'2007-08-14 04:42:00' AS beginn, '2007-08-17 17:25:00' AS ende UNION SELECT 220 AS fallnr,'2007-08-17 14:15:00' AS beginn, '2007-08-26 09:08:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-23 02:22:00' AS beginn, '2007-08-23 17:44:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-26 16:04:00' AS beginn, '2007-08-26 16:51:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-26 21:15:00' AS beginn, '2007-08-27 07:17:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-28 11:03:00' AS beginn, '2007-08-28 13:06:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-29 02:11:00' AS beginn, '2007-08-29 07:29:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-30 13:30:00' AS beginn, '2007-08-31 00:43:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 02:07:00' AS beginn, '2007-08-31 08:00:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 06:46:00' AS beginn, '2007-08-31 09:08:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 09:09:00' AS beginn, '2007-08-31 12:59:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 12:59:00' AS beginn, '2007-08-31 13:58:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 13:58:00' AS beginn, '2007-08-31 17:45:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 17:46:00' AS beginn, '2007-08-31 18:30:00' AS ende UNION SELECT 263 AS fallnr,'2007-08-31 18:31:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 263 AS fallnr,'2007-09-01 00:00:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-20 19:38:00' AS beginn, '2007-08-23 11:58:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-24 05:15:00' AS beginn, '2007-08-24 08:18:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-24 09:47:00' AS beginn, '2007-08-24 16:36:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-24 19:26:00' AS beginn, '2007-08-24 23:17:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-25 00:32:00' AS beginn, '2007-08-25 09:19:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-25 14:27:00' AS beginn, '2007-08-25 22:51:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-25 22:52:00' AS beginn, '2007-08-25 23:48:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-25 23:48:00' AS beginn, '2007-08-26 11:24:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-26 11:24:00' AS beginn, '2007-08-26 13:04:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-26 13:05:00' AS beginn, '2007-08-27 14:18:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-26 18:03:00' AS beginn, '2007-08-26 20:32:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-27 15:38:00' AS beginn, '2007-08-27 19:38:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-27 18:38:00' AS beginn, '2007-08-28 12:03:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-27 20:49:00' AS beginn, '2007-08-28 12:03:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-28 12:29:00' AS beginn, '2007-08-28 14:03:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-28 18:54:00' AS beginn, '2007-08-29 03:10:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 04:46:00' AS beginn, '2007-08-29 12:18:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 12:18:00' AS beginn, '2007-08-29 12:50:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 12:50:00' AS beginn, '2007-08-29 13:28:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 13:28:00' AS beginn, '2007-08-29 15:04:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 15:04:00' AS beginn, '2007-08-29 18:03:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 16:30:00' AS beginn, '2007-08-29 19:09:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 19:09:00' AS beginn, '2007-08-29 19:22:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 19:22:00' AS beginn, '2007-08-29 20:44:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-29 20:45:00' AS beginn, '2007-08-30 14:10:00' AS ende UNION SELECT 274 AS fallnr,'2007-08-30 14:38:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-21 08:04:00' AS beginn, '2007-08-22 11:24:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-22 14:34:00' AS beginn, '2007-08-22 18:08:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-22 18:24:00' AS beginn, '2007-08-23 04:45:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-23 08:18:00' AS beginn, '2007-08-23 14:00:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-23 15:46:00' AS beginn, '2007-08-23 18:01:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-23 22:54:00' AS beginn, '2007-08-24 00:44:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-24 08:23:00' AS beginn, '2007-08-24 08:36:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-24 09:30:00' AS beginn, '2007-08-24 11:42:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-24 11:42:00' AS beginn, '2007-08-24 21:37:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-24 21:33:00' AS beginn, '2007-08-24 22:18:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-24 23:23:00' AS beginn, '2007-08-25 19:28:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-25 11:05:00' AS beginn, '2007-08-25 22:10:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-25 22:10:00' AS beginn, '2007-08-26 02:05:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 02:05:00' AS beginn, '2007-08-26 07:51:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 07:51:00' AS beginn, '2007-08-26 10:51:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 10:52:00' AS beginn, '2007-08-26 12:34:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 12:34:00' AS beginn, '2007-08-26 14:48:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 15:01:00' AS beginn, '2007-08-26 18:17:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 18:17:00' AS beginn, '2007-08-26 22:12:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 22:13:00' AS beginn, '2007-08-26 22:57:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-26 23:02:00' AS beginn, '2007-08-27 08:12:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-27 08:12:00' AS beginn, '2007-08-27 20:48:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-27 20:49:00' AS beginn, '2007-08-28 07:26:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-28 07:26:00' AS beginn, '2007-08-28 21:30:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-28 21:30:00' AS beginn, '2007-08-29 02:57:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-29 03:00:00' AS beginn, '2007-08-29 10:40:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-29 10:48:00' AS beginn, '2007-08-29 13:14:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-29 13:14:00' AS beginn, '2007-08-30 22:31:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-29 22:14:00' AS beginn, '2007-08-30 05:22:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-30 22:32:00' AS beginn, '2007-08-31 06:01:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-31 06:01:00' AS beginn, '2007-08-31 22:58:00' AS ende UNION SELECT 311 AS fallnr,'2007-08-31 22:58:00' AS beginn, '2007-09-01 00:00:00' AS ende UNION SELECT 368 AS fallnr,'2007-08-25 17:29:00' AS beginn, '2007-08-25 18:10:00' AS ende UNION SELECT 368 AS fallnr,'2007-08-25 18:06:00' AS beginn, '2007-08-25 22:00:00' AS ende UNION SELECT 368 AS fallnr,'2007-08-25 22:00:00' AS beginn, '2007-09-01 00:00:00' AS ende ) abfrage;