MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access - Code Archiv
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 25.03.2013, 22:34   #1
ebs17
MOF Guru
MOF Guru
Standard Grundlagen - SQL ist leicht (3) - Kalendertabelle

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

' 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.
Code:

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.
Angehängte Dateien
Dateityp: zip Kalendertabelle.zip (41,5 KB, 281x aufgerufen)

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 00:30 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.