/* <-- zurück zu Zeitintervalle in SQL verarbeiten */ /* SQL Server: Schritte für die Datenaufbereitung in einem Skript */ /* Dieses Skript ist sehr performant. Es braucht nur wenige Sekunden. */ /* Dieser Befehl fügt 20.000 Tage (ca. 54 Jahre) ab 01.01.2000 in die Tages-Tabelle ein. Er braucht dafür weniger als 0,5 Sekunden */ TRUNCATE TABLE zeitintervall_tag; WITH zahlen_0_bis_9 AS ( SELECT 0 AS zahl UNION SELECT 1 AS zahl UNION SELECT 2 AS zahl UNION SELECT 3 AS zahl UNION SELECT 4 AS zahl UNION SELECT 5 AS zahl UNION SELECT 6 AS zahl UNION SELECT 7 AS zahl UNION SELECT 8 AS zahl UNION SELECT 9 AS zahl ) INSERT INTO zeitintervall_tag (tag) SELECT DATEADD(DAY, 10000 * t1.zahl + 1000 * t2.zahl + 100 * t3.zahl + 10 * t4.zahl + t5.zahl, CONVERT(DATETIME, '2000-01-01',120) ) AS tag FROM ( SELECT zahl FROM zahlen_0_bis_9 WHERE zahl <= 1 ) t1 INNER JOIN zahlen_0_bis_9 t2 ON 1 = 1 INNER JOIN zahlen_0_bis_9 t3 ON 1 = 1 INNER JOIN zahlen_0_bis_9 t4 ON 1 = 1 INNER JOIN zahlen_0_bis_9 t5 ON 1 = 1; /* Nun die Zeitintervalle sortieren. Dabei müssen diejenigen Zeitintervalle ausgeschlossen werden, die komplett innerhalb von einem anderen liegen. Diese Intervalle zählen für das Ergebnis sowieso nicht, und für die nächsten Schritte brauchen wir pro Fall eine Folge von Zeitintervallen, wo Beginn des Nachfolgers >= Beginn des Vorgängers und auch das Ende des Nachfolgers >= Ende des Vorgängers ist. */ TRUNCATE TABLE zeitintervall_sortiert; INSERT INTO zeitintervall_sortiert ( fallnr, beginn, ende ) SELECT fallnr, beginn, ende FROM zeitintervall_testdaten WHERE row_id NOT IN ( SELECT DISTINCT t1.row_id FROM zeitintervall_testdaten t1 INNER JOIN zeitintervall_testdaten t2 ON t1.fallnr = t2.fallnr AND t1.beginn > t2.beginn AND t1.ende < t2.ende ) ORDER BY fallnr, beginn, ende; /* Als nächstes werden die Stellen gekennzeichnet, wo der Beginn des Nachfolgeintervalls nach dem Ende des Vorgängers liegt, denn bis dorthin kann man alle Intervalle einer Fallnr als zusammenhängend betrachten und damit diesen Zeitraum vom Anfang bis zum Ende zählen. Mit dem nächsten Intervall beginnt ein neuer zusammenhängender Intervall. */ UPDATE t1 SET t1.next_beginn = t2.beginn FROM zeitintervall_sortiert t1 INNER JOIN zeitintervall_sortiert t2 ON t1.fallnr = t2.fallnr AND t1.row_id + 1 = t2.row_id AND t1.ende < t2.beginn; /* Die Länge der zusammenhängenden Intervalle wird nun nicht direkt ermittelt, sondern indirekt. Dafür wird erstens die Gesamtzeit eines Falles vom ersten Beginn bis zum letzten Ende eingetragen. Zweitens werden die Lücken in der Gesamtzeit eingetragen, so dass später die Längen der zusammenhängenden Intervalle daraus berechnet werden können. */ TRUNCATE TABLE zeitintervall_gesamt_und_luecken; INSERT INTO zeitintervall_gesamt_und_luecken (fallnr, beginn, beginntag, ende, endetag, luecke, faktor) SELECT fallnr, min_beginn, CONVERT(DATETIME, CONVERT(VARCHAR(10), min_beginn, 120), 120) AS beginntag, max_ende, CONVERT(DATETIME, CONVERT(VARCHAR(10), max_ende, 120), 120) AS endetag, luecke, faktor FROM ( /* zuerst den Gesamt-Zeitraum jedes Falles vom ersten Beginn bis zum letzten Ende eintragen */ SELECT fallnr, MIN(beginn) AS min_beginn, MAX(ende) AS max_ende, '' AS luecke, 1 AS faktor FROM zeitintervall_sortiert GROUP BY fallnr UNION ALL /* danach die Lücken zusätzlich eintragen und mit dem Faktor -1 versehen, damit sie subtrahiert werden können */ SELECT fallnr, ende, next_beginn, 'X' AS luecke, -1 AS faktor FROM zeitintervall_sortiert WHERE next_beginn IS NOT NULL ) abfrage; /* Für das Ergebnis zeigt der Faktor (1 oder -1) an, ob der jeweilige Zeitraum positiv oder negativ einfließen muss. */ TRUNCATE TABLE zeitintervall_ergebnis; INSERT INTO zeitintervall_ergebnis (fallnr, tag, minuten) SELECT t1.fallnr, t2.tag, SUM(t1.faktor * CASE WHEN t1.beginntag = t1.endetag THEN DATEDIFF(MINUTE, t1.beginn, t1.ende) WHEN t1.beginntag = t2.tag THEN 1440 - DATEDIFF(MINUTE, t2.tag, t1.beginn) WHEN t1.endetag = t2.tag THEN DATEDIFF(MINUTE, t2.tag, t1.ende) ELSE 1440 END ) AS minuten FROM zeitintervall_gesamt_und_luecken t1 INNER JOIN zeitintervall_tag t2 ON t1.endetag >= t2.tag AND t1.beginntag <= t2.tag GROUP BY t1.fallnr, t2.tag ORDER BY t1.fallnr, t2.tag; /* Zeilen mit Minuten = 0 löschen */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0;