Hallo,
ich knobele schon eine ganze Weile an einer Sache herum. Ich habe zwar schon eine Quick’n’dirty-Version laufen, aber ich hätte es gerne in “schön” - dass heißt möglichst in einem SQL-Statement.
Doch nun zur Sache:
ich erstelle eine Erfassung von Abwesenheiten von Mitarbeitern (Krankheit, Urlaub, …) und habe (im Wesentlichen - die “echten” sind natürlich komplexer, aber das tut hier nichts zur Sache) folgende Tabellen erstellt:
CREATE TABLE mitarbeiter(
id INTEGER PRIMARY KEY,
vorname TEXT,
name TEXT
);
INSERT INTO mitarbeiter VALUES (1, 'Martin', 'Meier');
INSERT INTO mitarbeiter VALUES (2, 'Nicole', 'Neumann');
CREATE TABLE abwesenheit_grund(
id INTEGER PRIMARY KEY,
bezeichnung TEXT
);
INSERT INTO abwesenheit_grund VALUES (1, 'Krankheit');
INSERT INTO abwesenheit_grund VALUES (2, 'Urlaub');
CREATE TABLE abwesenheit(
id SERIAL PRIMARY KEY,
mitarbeiter INTEGER REFERENCES mitarbeiter,
grund INTEGER REFERENCES abwesenheit_grund,
beginn TIMESTAMP,
ende TIMESTAMP,
fehler BOOLEAN DEFAULT false
);
INSERT INTO abwesenheit (mitarbeiter, grund, beginn, ende)
VALUES (1, 2, '2008-04-21 8:00', '2008-04-25 18:00');
INSERT INTO abwesenheit (mitarbeiter, grund, beginn, ende)
VALUES (2, 2, '2008-04-14 8:00', '2008-05-02 18:00');
INSERT INTO abwesenheit (mitarbeiter, grund, beginn, ende)
VALUES (1, 1, '2008-04-23 12:00', '2008-04-23 17:00');
Bei den Datensätzen sollte es natürlich keine Überlappungen geben, d.h. wenn für den Mitarbeiter 1 Urlaub vom 21.04 bis zum 25.04 eingegeben wurde und nun ein Krankheitstag am 23.04 - also während des Urlaubs - dann sollen die beiden Datensätze zur Korrektur herausgesucht werden, was ja auch einfach ist:
SELECT ueberlapp.id
FROM abwesenheit AS test,
abwesenheit AS ueberlapp
WHERE test.id = 3 AND
ueberlapp.mitarbeiter = test.mitarbeiter AND
ueberlapp.beginn < test.ende AND
ueberlapp.ende > test.beginn;
ich teste hier, ob es zum Eintrag mit der ID 3 einen überlappenden Eintrag gibt. Ergebnis lautet auch - wie erwartet:
id
----
1
3
(2 Zeilen)
Leider liefert mir diese Abfrage aber auch ein Ergebnis zurück, wenn es keinen überlappenden Eintrag gibt:
SELECT ueberlapp.id
FROM abwesenheit AS test,
abwesenheit AS ueberlapp
WHERE test.id = 2 AND
ueberlapp.mitarbeiter = test.mitarbeiter AND
ueberlapp.beginn < test.ende AND
ueberlapp.ende > test.beginn;
liefert mir
id
----
2
(1 Zeile)
was ich aber nicht haben möchte, da es ja keine Überlappung gibt.
Aufmerksame Zeitgenossen werden jetzt natürlich zurecht einwenden, dass ich den Datensatz, den ich prüfe auch ausschließen könnte:
SELECT ueberlapp.id
FROM abwesenheit AS test,
abwesenheit AS ueberlapp
WHERE test.id = 2 AND
test.id <> ueberlapp.id AND
ueberlapp.mitarbeiter = test.mitarbeiter AND
ueberlapp.beginn < test.ende AND
ueberlapp.ende > test.beginn;
liefert mir auch
id
----
(0 Zeilen)
ich möchte aber gerne alle Datensätze aufgerufen haben, die sich überlappen.
Der Hintergrund ist folgender: nach dem Einfügen eines Datensatzes möchte ich gerne so etwas machen:
INSERT INTO abwesenheit (mitarbeiter, grund, beginn, ende)
VALUES(2, 1, '2008-04-10 8:00', '2008-04-10 17:00');
UPDATE abwesenheit
SET fehler = true
WHERE id IN (
SELECT ueberlapp.id
FROM abwesenheit AS test,
abwesenheit AS ueberlapp
WHERE test.id = currval('abwesenheit_id_seq') AND
ueberlapp.mitarbeiter = test.mitarbeiter AND
ueberlapp.beginn < test.ende AND
ueberlapp.ende > test.beginn
);
womit dann im Anschluss an das INSERT bei allen überlappenden Einträgen - und eben auch bei dem neuen Datensatz - das Feld abwesenheit.fehler auf ‘true’ gesetzt wird und nichts geschieht, wenn es keine überlappenden Einträge gibt.
Wenn es keine triviale Lösung ohne Funktion gibt, werde ich wohl doch plpgsql bemühen müssen. 
Ich gebe zu, dass meine Frage etwas akademisch ist, aber ich finde es einfach “schöner” wenn es mit reinem SQL ginge.
Für Anregungen und/oder Hinweise bin ich äußerst dankbar…