Eine Kalendertabelle in SQL Server erstellen

Für eine Vielzahl an Geschäftsprozessen und -Anwendungen ist es hilfreich, Kalendertabellen in SQL Server zu implementieren und sie beispielsweise zu Berichtszwecken zu verwenden. Oftmals treten aber Schwierigkeiten bei der Einrichtung auf, die Anwender dazu zwingen, Eingaben in Datendimensionstabellen manuell vorzunehmen.

Wir erklären Ihnen heute, wie Sie die Schwierigkeiten umgehen können und im Handumdrehen eine leistungsstarke, leicht anpassbare Kalendertabelle erstellen.

In Bezug auf Größe, Speichernutzung und Leistungsfähigkeit kann die Erstellung einer Kalendertabelle deutlich günstiger sein. Insbesondere da der zugrunde liegende Speicher im Vergleich zur Verwendung von datumsbezogener Ermittlungsfunktionen, immer größer und schneller wird. Über Jahrzehnte gespeicherte Daten benötigen vergleichsweise geringe MB, in komprimierter Version sogar noch weniger.

Um Mehrdeutigkeit zu vermeiden, verwenden wir bei der Einrichtung explizite Bezeichnungen wie beispielsweise DATEFORMAT und LANGUAGE, stellen die richtige Zeit bzw. präferierte Wochen- & Quartalsanfänge ein, sowie die korrekten Monats- & Tagesbezeichnungen. Diese Einstellungen können jedoch nach entsprechenden geografischen Standorten variieren. Es handelt sich bei dem Vorgang um eine einmalige Population. Diese ist auch soweit unbedenklich, sofern wir den CTE-Ansatz immer im Auge behalten. Wir materialisieren dafür alle Spalten auf unserer Festplatte, anstatt uns auf berechnete Spalten zu verlassen.

Exkurs

Die Abkürzung CTE beschreibt die “common table expression”. In einer rekursiven Variante verweist die Abfrage grundlegend auf sich selbst. Auf diese Weise wird der CTE wiederholt ausgeführt und gibt Teilmengen von Daten zurück, bis die vollständige Ergebnismenge zurückgegeben wird.

Einfaches rekursives SQL Server-CTE-Beispiel:
In diesem Beispiel verwenden wir einen rekursiven CTE, um Wochentage von Monday bis zurückzugeben Saturday:

WITH cte_numbers(n, weekday) 
AS (
    SELECT 
        0, 
        DATENAME(DW, 0)
    UNION ALL
    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6
)
SELECT 
    weekday
FROM 
    cte_numbers;

Hier ist die Ergebnismenge:

In unserem weiteren Beispiel erstellen wir eine Datendimensionstabelle, beginnend mit dem 01.01.2010 über einen Zeitraum von 30 Jahren. Da es sich hier ebenfalls um einen rekursiven CTE handelt, gibt uns die folgende Abfrage die Anzahl der Tage zwischen Start- und Enddatum wieder:

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
SET DATEFIRST  7, -- 1 = Monday, 7 = Sunday
    DATEFORMAT mdy, 
    LANGUAGE   US_ENGLISH;
-- assume the above is here in all subsequent code blocks.

DECLARE @StartDate  date = '20100101';

DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
)
SELECT n FROM seq
ORDER BY n 
OPTION (MAXRECURSION 0);

Mainzer Datenfabrik - Eine Kalendertabelle in SQL Server erstellen

Wurde die Abfrage erfolgreich durchgeführt, bekommen wir nun die Anzahl der Tage angezeigt. Ergänzen wir unser Vorhaben jedoch um ein weiteres CTE, können wir uns mit folgender Abfrage auch zusätzlich den Datumsbereich zurückgeben lassen:

DECLARE @StartDate date = '20100101' ; 

DECLARE @CutoffDate date = DATEADD ( DAY , -1 , DATEADD ( YEAR , 30 , @StartDate ));    

;; WITH seq ( n ) AS ( SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF ( TAG , @StartDate , @CutoffDate ) ), 
d ( d ) AS ( SELECT DATEADD ( DAY , n , @StartDate ) FROM seq
 ) SELECT d FROM d
  

         
  

    
ORDER BY d
 OPTION ( MAXRECURSION 0 );

Ab dem jetzigen Zeitpunkt ergänzen wir die Kalendertabellen mit weiteren wichtigen Informationen. Damit sparen wir uns eine erneute inline Berechnung. Zur Extrahierung der Berechnungen, erstellen wir als Zwischenschritt eine weitere CTE-Abfrage:

DECLARE @StartDate  date = '20100101';

DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
)
SELECT * FROM src
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

und erhalten folgende Ausgabe:

Angenommen, wir möchten unser Geschäftsjahr anders darlegen, können wir für die Berechnung weitere Spalten wie bspw. “Jahr” oder “Quartal” hinzufügen. Startet das Geschäftsjahr beispielsweise am 01.10., könnten wir ein DATEADD hinzufügen:

;WITH q AS (SELECT d FROM 
(
    VALUES('20200101'),
          ('20200401'),
          ('20200701'),
          ('20201001')
    ) AS d(d))
SELECT
  d, 
  StandardQuarter        = DATEPART(QUARTER, d),
  LateFiscalQuarter      = DATEPART(QUARTER, DATEADD(MONTH, -9, d)),
  LateFiscalQuarterYear  = YEAR(DATEADD(MONTH, -9, d)),
  EarlyFiscalQuarter     = DATEPART(QUARTER, DATEADD(MONTH,  3, d)),
  EarlyFiscalQuarterYear = YEAR(DATEADD(MONTH,  3, d))
FROM q;

Leistungspakete der Mainzer Datenfabrik

Als professioneller SQL Server Support und zertifizierter Microsoft Partner unterstützen wir Sie in allen Fragen und individuellen Problemen rund um Ihre Serverumgebung, egal ob vor Ort oder remote. Überzeugen Sie sich selbst von unserem vielfältigen Angebot und den individuellen Leistungspaketen.

SQL Server SQL Server auf Azure

Mit diesem Vorgang können wir viel mehr Details über bestimmte Daten erfahren, beispielsweise ob es in eine bestimmte Geschäftsperiode fällt oder ein Schaltjahr ist. Hier können wir uns ganz individuell und unseren Bedürfnissen gerecht einrichten:

DECLARE @StartDate  date = '20100101';

DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
dim AS
(
  SELECT
    TheDate, 
    TheDay,
    TheDaySuffix        = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE 
                            CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
                            WHEN '3' THEN 'rd' ELSE 'th' END END),
    TheDayName,
    TheDayOfWeek,
    TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
                            (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    IsWeekend           = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
                            THEN 1 ELSE 0 END,
    TheWeek,
    TheISOweek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheISOYear          = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1 
                            WHEN TheMonth = 12 AND TheISOWeek = 1  THEN -1 ELSE 0 END,      
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheLastOfYear,
    IsLeapYear          = CONVERT(bit, CASE WHEN (TheYear % 400 = 0) 
                            OR (TheYear % 4 = 0 AND TheYear % 100 <> 0) 
                            THEN 1 ELSE 0 END),
    Has53Weeks          = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    Has53ISOWeeks       = CASE WHEN DATEPART(WEEK,     TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                          + CONVERT(char(4), TheYear),
    Style101            = CONVERT(char(10), TheDate, 101),
    Style103            = CONVERT(char(10), TheDate, 103),
    Style112            = CONVERT(char(8),  TheDate, 112),
    Style120            = CONVERT(char(10), TheDate, 120)
  FROM src
)
SELECT * FROM dim
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

Sind wir zufrieden mit der Ausgabe, können wir folgende zwei Befehle ausführen:

SELECT * FROM dim

SELECT * INTO dbo.DateDimension FROM dim

Zusätzlich könnten wir an dieser Stelle unseren geclusterten Primärschlüssel oder weitere Indizes hinzufügen:

CREATE UNIQUE CLUSTERED INDEX PK_DateDimension ON dbo.DateDimension(TheDate);

Um eine Vorstellung davon zu bekommen, wie viel Speicherplatz von dieser Tabelle wirklich benötigt wird: → Das Maximum liegt bei etwa 2 MB mit einem regulären geclusterten Index, der auf der Spalte “TheDate” definiert ist, bis hinunter zu 500 KB für einen geclusterten Columnstore-Index, der mit COLUMNSTORE_ARCHIVE komprimiert ist.

Als nächstes möchten wir eine weitere Spalte ISHOLIDAY einfügen, dafür müssen wir eine neue Tabelle wie folgt erstellen:

CREATE TABLE dbo.HolidayDimension
(
  TheDate date NOT NULL,
  HolidayText nvarchar(255) NOT NULL,
  CONSTRAINT FK_DateDimension FOREIGN KEY(TheDate) REFERENCES dbo.DateDimension(TheDate)
);

CREATE CLUSTERED INDEX CIX_HolidayDimension ON dbo.HolidayDimension(TheDate);
GO

Diese Tabelle ermöglicht Ihnen, mehr als einen Feiertag für ein bestimmtes Datum zu haben. Es erlaubt sogar mehrere ganze Kalender mit jeweils einem eigenen Satz von Feiertagen, die Sie beispielsweise ganz individuell zusammenstellen können. Bei individuellen, nicht gesetzlich festgelegten Feiertagen hängen Faktoren wie andere Länder, Bundesländer oder vielleicht auch eigene firmeninterne Feiertage zusammen. Die bundesweiten gesetzlichen Feiertage jedoch können Sie mit folgender Abfrage einbauen:

;WITH x AS 
(
  SELECT
    TheDate,
    TheFirstOfYear,
    TheDayOfWeekInMonth, 
    TheMonth, 
    TheDayName, 
    TheDay,
    TheLastDayOfWeekInMonth = ROW_NUMBER() OVER 
    (
      PARTITION BY TheFirstOfMonth, TheDayOfWeek
      ORDER BY TheDate DESC
    )
  FROM dbo.DateDimension
),
s AS
(
  SELECT TheDate, HolidayText = CASE
  WHEN (TheDate = TheFirstOfYear) 
    THEN 'New Year''s Day'
  WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 1 AND TheDayName = 'Monday')
    THEN 'Martin Luther King Day'    -- (3rd Monday in January)
  WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 2 AND TheDayName = 'Monday')
    THEN 'President''s Day'          -- (3rd Monday in February)
  WHEN (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5 AND TheDayName = 'Monday')
    THEN 'Memorial Day'              -- (last Monday in May)
  WHEN (TheMonth = 7 AND TheDay = 4)
    THEN 'Independence Day'          -- (July 4th)
  WHEN (TheDayOfWeekInMonth = 1 AND TheMonth = 9 AND TheDayName = 'Monday')
    THEN 'Labour Day'                -- (first Monday in September)
  WHEN (TheDayOfWeekInMonth = 2 AND TheMonth = 10 AND TheDayName = 'Monday')
    THEN 'Columbus Day'              -- Columbus Day (second Monday in October)
  WHEN (TheMonth = 11 AND TheDay = 11)
    THEN 'Veterans'' Day'            -- (November 11th)
  WHEN (TheDayOfWeekInMonth = 4 AND TheMonth = 11 AND TheDayName = 'Thursday')
    THEN 'Thanksgiving Day'          -- (Thanksgiving Day ()fourth Thursday in November)
  WHEN (TheMonth = 12 AND TheDay = 25)
    THEN 'Christmas Day'
  END
  FROM x
  WHERE 
    (TheDate = TheFirstOfYear)
    OR (TheDayOfWeekInMonth = 3     AND TheMonth = 1  AND TheDayName = 'Monday')
    OR (TheDayOfWeekInMonth = 3     AND TheMonth = 2  AND TheDayName = 'Monday')
    OR (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5  AND TheDayName = 'Monday')
    OR (TheMonth = 7 AND TheDay = 4)
    OR (TheDayOfWeekInMonth = 1     AND TheMonth = 9  AND TheDayName = 'Monday')
    OR (TheDayOfWeekInMonth = 2     AND TheMonth = 10 AND TheDayName = 'Monday')
    OR (TheMonth = 11 AND TheDay = 11)
    OR (TheDayOfWeekInMonth = 4     AND TheMonth = 11 AND TheDayName = 'Thursday')
    OR (TheMonth = 12 AND TheDay = 25)
)
INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT TheDate, HolidayText FROM s 
UNION ALL 
SELECT DATEADD(DAY, 1, TheDate), 'Black Friday'
  FROM s WHERE HolidayText = 'Thanksgiving Day'
ORDER BY TheDate;

Mit einem besonderen Blick sollte man auch – zwar gesetzliche, aber nicht datumgebundene Feiertage betrachten (Ostern, Fastnacht usw.):

CREATE FUNCTION dbo.GetEasterHolidays(@TheYear INT) 
RETURNS TABLE
WITH SCHEMABINDING
AS 
RETURN 
(
  WITH x AS 
  (
    SELECT TheDate = DATEFROMPARTS(@TheYear, [Month], [Day])
      FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
      FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
      FROM (SELECT DaysToSunday = paschal - ((@TheYear + (@TheYear / 4) + paschal - 13) % 7)
      FROM (SELECT paschal = epact - (epact / 28)
      FROM (SELECT epact = (24 + 19 * (@TheYear % 19)) % 30) 
        AS epact) AS paschal) AS dts) AS m) AS d
  )
  SELECT TheDate, HolidayText = 'Easter Sunday' FROM x
    UNION ALL SELECT DATEADD(DAY, -2, TheDate), 'Good Friday'   FROM x
    UNION ALL SELECT DATEADD(DAY,  1, TheDate), 'Easter Monday' FROM x
);
GO

Mit der folgenden Abfrage können Sie beispielsweise die Osterfeiertage in die Tabelle einfügen:

INSERT dbo.HolidayDimension(TheDate, HolidayText)
  SELECT d.TheDate, h.HolidayText
    FROM dbo.DateDimension AS d
    CROSS APPLY dbo.GetEasterHolidays(d.TheYear) AS h
    WHERE d.TheDate = h.TheDate;

Abschließend lassen wir uns nun nur noch die Ansicht aller erstellen Tabellen anzeigen:

CREATE VIEW dbo.TheCalendar
AS 
  SELECT
    d.TheDate,
    d.TheDay,
    d.TheDaySuffix,
    d.TheDayName,
    d.TheDayOfWeek,
    d.TheDayOfWeekInMonth,
    d.TheDayOfYear,
    d.IsWeekend,
    d.TheWeek,
    d.TheISOweek,
    d.TheFirstOfWeek,
    d.TheLastOfWeek,
    d.TheWeekOfMonth,
    d.TheMonth,
    d.TheMonthName,
    d.TheFirstOfMonth,
    d.TheLastOfMonth,
    d.TheFirstOfNextMonth,
    d.TheLastOfNextMonth,
    d.TheQuarter,
    d.TheFirstOfQuarter,
    d.TheLastOfQuarter,
    d.TheYear,
    d.TheISOYear,
    d.TheFirstOfYear,
    d.TheLastOfYear,
    d.IsLeapYear,
    d.Has53Weeks,
    d.Has53ISOWeeks,
    d.MMYYYY,
    d.Style101,
    d.Style103,
    d.Style112,
    d.Style120,
    IsHoliday = CASE WHEN h.TheDate IS NOT NULL THEN 1 ELSE 0 END,
    h.HolidayText
  FROM dbo.DateDimension AS d
  LEFT OUTER JOIN dbo.HolidayDimension AS h
  ON d.TheDate = h.TheDate;

Nun sollten Sie eine funktionale Kalenderansicht haben, die Sie für Ihre Geschäftsanforderungen nutzen können.

Fazit

Abschließend halten wir fest, dass zwar die Einrichtung einer Kalendertabelle auf den ersten Blick umfangreich erscheinen mag, sie sich jedoch letzten Endes lohnen wird. Es gibt dafür einige Möglichkeiten, wie wir Ihnen aufgezeigt haben. Letztlich müssen Sie eruieren, wie oft die Werte berechnet werden müssen und ob Sie den zusätzlichen Speicherplatz auf der Festplatte dafür zur Verfügung stellen möchten. Wenn Sie die Enterprise Edition von SQL Server 2014 oder höher verwenden, könnten Sie die Verwendung von In-Memory-OLTP in Betracht ziehen und möglicherweise sogar eine nicht dauerhafte Tabelle, die Sie mithilfe einer Startprozedur neu aufbauen. Alternativ könnten Sie die Kalendertabelle in einer beliebigen Version in eine eigene Datenbank verschieben und sie nach der anfänglichen Population als schreibgeschützt markieren. Dadurch wird die Tabelle nicht gezwungen, die ganze Zeit im Speicher zu bleiben.

Eine Kalendertabelle in SQL Server erstellen

Exkurs

Leistungspakete der Mainzer Datenfabrik

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.