User Tools

Site Tools


sql:tutorials:intersectperiodes

[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:

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

sql/tutorials/intersectperiodes.txt · Last modified: 27.08.2015 09:12:09 by yaslaw