PDA

Vollständige Version anzeigen : SQL ist leicht (3) - Kalendertabelle


ebs17
25.03.2013, 21:34
Eine Kalendertabelle

Wozu braucht man denn eine Kalendertabelle? Man kann sich doch alles per VBA berechnen, was da so anfällt. Nun, ein Kalender ändert sich praktisch nicht. Daher kann man ihn als einmaligen Aufwand auch vorausberechnen und dann zum Nachschlagen benutzen. Recht oft erzielt man mit dem Nachschlagen erhebliche Vorteile gegenüber einem jeweiligen Neuberechnen, unter der Voraussetzung, dass Kalendertabelle und das Nachschlagen an sich optimal gestaltet sind.

Dazu enthält die Kalendertabelle als Primary Key ein Datumsfeld, das fortlaufend die Kalendertage eines ausreichenden Zeitraumes enthält. Dieses Feld wird man später mit einem Datumsfeld seiner Stammdatentabelle verknüpfen. In weiteren möglichen Feldern legt man berechnete Formate aus dem Datum ab (nach Bedarf: Monat, Jahr, Kalenderwoche, Wochentag, ...). Diese Felder sollte man alle indizieren (um beim Nachschlagen einen zählbaren Gewinn zu erzielen).

Anwendungsbeispiel Umsatz
Eine Umsatztabelle tblUmsatz enthält die Felder Buchungstag und Umsatz mit Einträgen über mehrere Jahre. Es sollen die monatlichen Umsätze ermittelt werden, also übersichtlich die Monate gruppiert und die Umsätze summiert werden. Folgende Varianten werden gegenüber gestellt:
' Gruppierungsmerkmal wird per Format des Datums erzeugt => String
SELECT
Format(U.Buchungstag, 'yyyymm') AS YYYYMM,
Sum(U.Umsatz) AS UmsatzSumme
FROM
tblUmsatz AS U
GROUP BY
Format(U.Buchungstag, 'yyyymm')
ORDER BY
1

' Gruppierungsmerkmal wird erzeugt aus Year(Buchungstag)*100 + Month(Buchungstag) => Zahl
SELECT
Year(U.Buchungstag) * 100 + Month(U.Buchungstag) AS YYYYMM,
Sum(U.Umsatz) AS UmsatzSumme
FROM
tblUmsatz AS U
GROUP BY
Year(U.Buchungstag) * 100 + Month(U.Buchungstag)
ORDER BY
1

' Gruppierungsmerkmal wird in der Kalendertabelle nachgeschlagen
SELECT
K.kJahr,
K.kMonat,
Sum(U.Umsatz) AS Umsatzsumme
FROM
tblUmsatz AS U
INNER JOIN tblH_Kalender AS K
ON U.Buchungstag = K.kTag
GROUP BY
K.kJahr,
K.kMonat
ORDER BY
1,
2

Auswertung:

- Die Formatvariante ist üblicherweise erste Wahl. Berechtigt?

- Die Zahlenvariante benötigt nur etwa 78 Prozent der Zeit der Formatvariante. Einleuchtende Erklärung: Mit Zahlen lässt es sich besser rechnen als mit Strings. Das zeigt sich dann auch in einer schnelleren Gruppierung.

- Die Kalendervariante benötigt nur etwa 38 Prozent der Zeit der Zahlenvariante und nur etwa 30 Prozent der Zeit der Formatvariante, trotzdem dass eine zweite Tabelle verwendet und verknüpft wird und deren Daten mitverwendet werden. Die Erklärung ist auch hier wieder einfach: Während in den beiden ersten Varianten über einen berechneten Wert gruppiert wird und somit dafür ein Indexnutzung nicht stattfinden kann, werden in der Kalendervariante durch das Nachschlagen die Gruppierungsmerkmale mit Indexierung in die Abfrage hereingeholt und gewinnbringend genutzt.

Anwendungsbeispiel Arbeitstage
In einer Anwesenheitstabelle werden Anwesenheitszeiträume von Personen erfasst. Jetzt soll ermittelt werden, wieviel Tage ohne Wochenenden pro Monat und Person angefallen sind.
SELECT
A.Person_ID,
K.kJahr,
K.kMonat,
COUNT(K.kTag) AS Anwesenheit
FROM
tblAnwesenheit AS A,
(
SELECT
kTag,
kMonat,
kJahr
FROM
tblH_Kalender
WHERE
kWoTag < 6
) AS K
WHERE
K.kTag BETWEEN A.Beginn
AND
A.Ende
GROUP BY
A.Person_ID,
K.kJahr,
K.kMonat

Und nun kann man seine Kreativität fliegen lassen, sei es, um weitere Anwendungsfälle zu finden, oder sei es, um seine Kalendertabelle aufzurüsten mit Informationen zu Feiertagen oder mit Informationen zu vom Kalenderjahr abweichendem Geschäftsjahr.

Die beigefügte Demo zeigt Tabellen und Versuchsanordnung.