SQL für lückenlose Historie

Wenn man vor der Aufgabe steht, eine lückenlose Historie zu verwenden, greift man schnell zu Datumsfeldern „gültig von“ und „gültig bis“. Das Problem dabei ist, dass man sicherstellen muss, dass weder Lücken noch Überschneidungen entstehen. Beispiele für so eine Historie können Gehälter oder Preise sein, deren Betrag man zu einem bestimmten Datum abfragen möchte.

Eine einfache und elegante Methode diese Bedingungen sicherzustellen ist die Verwendung nur eines „gültig ab“-Datums. Dann ist dieser Wert gültig bis zum nächsten „gültig ab“-Wert. Diesen Wert kann man mit einer Subquery abfragen.

Hier ein Beispiel. Es geht um die Bestimmung eines Preises zu einem Artikel an einem Datum (z.B. Verkaufsdatum). Wir brauchen eine Tabelle mit den Artikeln und eine für die Preise:

Basierend auf der Tabelle mit den Preisen erstellt man eine Abfrage, die den Gültigkeitszeitraum zurückgibt. Für das „gültig bis“-Datum verwendet man eine Subquery, die das Minimum des „gültig ab“-Datums zurückgibt, das größer sein muss als das aktuelle „gültig ab“-Datum.

gültig bis: (SELECT min([gültig ab]) FROM tblArtikelPreise as subQueryBis WHERE subQueryBis.id_artikel = tblArtikelPreise.id_artikel and subQueryBis.[gültig ab]>tblArtikelPreise.[gültig ab])

Wie man sieht, hat der letzte gültige Preis kein „gültig bis“. Um die Abfrage aber später in Abfragen nutzen zu können, sollte man dort ein Datum zurückgeben. Ich verwende dafür immer den 31.12.3000. Für Access sieht die Felddefinition dann so aus:

gültig bis: Nz((SELECT min([gültig ab]) FROM tblArtikelPreise as subQueryBis WHERE subQueryBis.id_artikel = tblArtikelPreise.id_artikel and subQueryBis.[gültig ab]>tblArtikelPreise.[gültig ab]);#31.12.3000#)

Für MySQL sieht das Feld z.B. so aus:

cast(
   ifnull(
   (
     SELECT
        min(`preise`.`gültig ab`)
     FROM
        `tblArtikelPreise` `preise`
     WHERE
        (
           (
              `preise`.`id_artikel` = `tblArtikelPreise`.`id_artikel`
           )
           AND (
              `preise`.`gültig ab` > `tblArtikelPreise`.`gültig ab`
           )
       )
   ),
   '3000-12-31'
   ) AS date
) AS `bis`

Das Resultat der Abfrage sieht dann so aus:

Diese Abfrage kann man dann in der eigentlichen Preisabfrage verwenden. Access kann leider keine anderen JOINs als „=“ darstellen. Will man die Abfrage weiterhin mit dem Editor bearbeiten können muss man den Filter auf das Datum als Bedingung einbauen, was die Abfrage langsamer macht. Die sieht dann z.B. so aus:

Schneller geht es, wenn man die Datums-Bedingung im JOIN verarbeitet. MySQL und der SQL-Server können diese JOINs im Editor darstellen (Oracle bestimmt auch). Access kann diese JOINs immerhin verarbeiten. Man muss diese dann direkt im SQL angeben:

Das Ergebnis sieht dann genauso aus:

Viel Spaß.