======[SQL] Perioden vergleichen======
Wenn man 2 Perioden vergleichen will, wird es relativ komplex. Darum hier mal einige Ansätze
===== Kompatibilität =====
Diese Lösung ist getestet für:
* MySQL
* Oracle DB
* MS Access. Hier müssen aber die 2 Userfunktionen [[vba:functions:greatest]] und [[vba:functions:least]] nach VBA importiert werden.
=====Datengrundlage der Beispiele=====
Die folgenden 2 Tabellen verwende ich in alle meinen Beispielen
^ tbl_periode_1 ^^^
^ id_1 ^ start_1 ^ end_1 ^
| 1 | 2012-02-01 | 2012-02-28 |
| 2 | 2012-02-15 | 2012-03-15 |
| 3 | 2012-04-01 | 2012-04-15 |
^ tbl_periode_2 ^^^
^ id_2 ^ start_2 ^ end_2 ^
| 1 | 2012-03-20 | 2012-04-02 |
| 2 | 2012-02-15 | 2012-02-15 |
| 3 | 2012-01-01 | 2012-02-02 |
| 4 | 2012-01-01 | 2012-12-31 |
=====Nur Überschneidende Perioden ausgeben=====
Als erstes Beispiel interessiert uns, welche Perioden_2 sich mit welchen Perioden_1 überschneiden. Also eine einfache Ausfilterung
SELECT
p1.id_1,
p1.start_1,
p1.end_1,
p2.id_2,
p2.start_2,
p2.end_2
FROM
tbl_periode_1 AS p1,
tbl_periode_2 AS p2
WHERE
LEAST(end_2, end_1) >= GREATEST(start_2, start_1)
ORDER BY
id_1,
id_2;
Das Resultat sieht dann so aus
^ Welche Periode 2 in welcher Periode 1 vorhanden ist ^^^^^^
| Periode 1 ||| Periode 2 |||
^ id_1 ^ start_1 ^ end_1 ^ id_2 ^ start_2 ^ end_2 ^
| 1 | 2012-02-01 | 2012-02-28 | 2 | 2012-02-15 | 2012-02-15 |
| 1 | 2012-02-01 | 2012-02-28 | 3 | 2012-01-01 | 2012-02-02 |
| 1 | 2012-02-01 | 2012-02-28 | 4 | 2012-01-01 | 2012-12-31 |
| 2 | 2012-02-15 | 2012-03-15 | 2 | 2012-02-15 | 2012-02-15 |
| 2 | 2012-02-15 | 2012-03-15 | 4 | 2012-01-01 | 2012-12-31 |
| 3 | 2012-04-01 | 2012-04-15 | 1 | 2012-03-20 | 2012-04-02 |
| 3 | 2012-04-01 | 2012-04-15 | 4 | 2012-01-01 | 2012-12-31 |
=====Schnittperioden ermitteln=====
Wenn sich 2 Perioden überschneiden, dann interessiert mich jetzt die sich überschneidende Periode. Also von wann und bis wann die Periode 1 und die Periode 2 sich überschneiden.
Das SQL ist eigentlich dasselbe wie zuvor. Ich ergänze es nur noch um die Berechnung der Anzahl Tage sowie das Start und End-Datum
SELECT
-- Beide Perioden auslesen
p1.id_1,
p1.start_1,
p1.end_1,
p2.id_2,
p2.start_2,
p2.end_2,
-- Die länge der Schnittperiode ermitteln
DATEDIFF(LEAST(end_2, end_1), GREATEST(start_2, start_1))+1 AS days,
-- Start und Endzeit von der Resultat-Periode berechnen
GREATEST(start_2, start_1) AS start_date,
LEAST(end_2, end_1) AS end_date
FROM
tbl_periode_1 AS p1,
tbl_periode_2 AS p2
WHERE
LEAST(end_2, end_1) >= GREATEST(start_2, start_1)
ORDER BY
id_1,
id_2;
^ Schnittperioden ^^^^^^^^^^
| Periode 1 ||| Periode 2 ||| Schnittperiode ||||
^ id_1 ^ start_1 ^ end_1 ^ id_2 ^ start_2 ^ end_2 ^ days ^ start_date ^ end_date ^
| 1 | 2012-02-01 | 2012-02-28 | 2 | 2012-02-15 | 2012-02-15 | 1 | 2012-02-15 | 2012-02-15 |
| 1 | 2012-02-01 | 2012-02-28 | 3 | 2012-01-01 | 2012-02-02 | 2 | 2012-02-01 | 2012-02-02 |
| 1 | 2012-02-01 | 2012-02-28 | 4 | 2012-01-01 | 2012-12-31 | 28 | 2012-02-01 | 2012-02-28 |
| 2 | 2012-02-15 | 2012-03-15 | 2 | 2012-02-15 | 2012-02-15 | 1 | 2012-02-15 | 2012-02-15 |
| 2 | 2012-02-15 | 2012-03-15 | 4 | 2012-01-01 | 2012-12-31 | 30 | 2012-02-15 | 2012-03-15 |
| 3 | 2012-04-01 | 2012-04-15 | 1 | 2012-03-20 | 2012-04-02 | 2 | 2012-04-01 | 2012-04-02 |
| 3 | 2012-04-01 | 2012-04-15 | 4 | 2012-01-01 | 2012-12-31 | 15 | 2012-04-01 | 2012-04-15 |
=====Code um die Testdaten zu erstellen =====
DROP TABLE tbl_periode_1;
CREATE TABLE tbl_periode_1
SELECT 1 AS id_1, DATE('2012-02-01') AS start_1, DATE('2012-02-28') AS end_1
UNION ALL SELECT 2, DATE('2012-02-15'), DATE('2012-03-15')
UNION ALL SELECT 3, DATE('2012-04-01'), DATE('2012-04-15');
DROP TABLE tbl_periode_2;
CREATE TABLE tbl_periode_2
SELECT 1 AS id_2, DATE('2012-03-20') AS start_2, DATE('2012-04-02') AS end_2
UNION ALL SELECT 2, DATE('2012-02-15'), DATE('2012-02-15')
UNION ALL SELECT 3, DATE('2012-01-01'), DATE('2012-02-02')
UNION ALL SELECT 4, DATE('2012-01-01'), DATE('2012-12-31');
===== Spielwiese =====
*[[http://sqlfiddle.com/#!2/e9d3d7/1|Bsp. Nur Überschneidende Perioden ausgeben bei sqlfiddle.com]]
*[[http://sqlfiddle.com/#!2/e9d3d7/2|Bsp. Schnittperioden ermitteln bei sqlfiddle.com]]