Zur Berechnung von Kennzahlen, die von der genauen Anzahl der Arbeitstage abhängen, ist es notwendig, diese im Modell korrekt abzubilden. Die Bundesländer in Deutschland haben unterschiedlich viele Feiertage, was die Anzahl der Arbeitstage beeinflusst. In diesem Artikel wird gezeigt, wie mit Hilfe der von ETL zur Verfügung gestellten Funktionen dbo.F_BC_Holiday und dbo.F_BC_Holiday2 die Arbeitstage pro Bundesland ermittelt werden können.

Arbeitstagskalender

In manchen Projekten ist es erforderlich, Arbeitstage tagesgenau abzubilden. Dabei sind die arbeitsfreien Tage exakt abzubilden – dies können Samstage, Sonntage und auch Feiertage sein. Da die Bundesländer in Deutschland keine einheitlichen Feiertage haben, müssen diese jeweils pro Bundesland ermittelt werden. Der vorliegende Blog-Beitrag zeigt, wie dies mit Hilfe der von ETL zur Verfügung gestellten Funktionen dbo.F_BC_Holiday und dbo.F_BC_Holiday2 unkompliziert umgesetzt werden kann.

Funktion F_BC_Holiday

dbo.F_BC_Holiday (@Year INT, @include_weekend INT = 0)

Parameter:

@Year – Kalenderjahr, für welches die Feiertage ermittelt werden soll

@include_weekend – Wochenenden in Ermittlung einschließen = 1 oder nicht = 0

Beispiel:

SELECT * FROM dbo.F_BC_Holiday (2024, 1)

Ergebnis:

HolidayDate                HolidayDesc

2024-01-01 00:00:00 Neujahrstag

2024-01-06 00:00:00 Heilige Drei Könige (BW, BY, ST)

2024-03-29 00:00:00 Karfreitag

2024-03-31 00:00:00 Ostersonntag

2024-04-01 00:00:00 Ostermontag

2024-05-01 00:00:00 Tag der Arbeit

2024-05-09 00:00:00 Christi Himmelfahrt

2024-05-19 00:00:00 Pfingstsonntag

2024-05-20 00:00:00 Pfingstmontag

2024-05-30 00:00:00 Fronleichnam (BW, BY, HE, NW, RP, SL)

2024-08-15 00:00:00 Mariä Himmelfahrt (SL)

2024-10-03 00:00:00 Tag der deutschen Einheit

2024-10-31 00:00:00 Reformationstag (BB, HB, HH, MV, NI, SN, ST, SH, TH)

2024-11-01 00:00:00 Allerheiligen (BW, BY, NW, RP, SL)

2024-11-20 00:00:00 Buß- und Bettag (SN)

2024-12-25 00:00:00 1. Weihnachtstag

2024-12-26 00:00:00 2. Weihnachtstag

2024-01-07 00:00:00 Wochenende

2024-01-13 00:00:00 Wochenende

…

(alle Wochenenden bis Ende Dezember 2024 folgen in der Ausgabe)

Die Funktion gibt die Feiertage nicht pro Bundesland aus, sondern vermerkt hinter dem Namen des jeweiligen Feiertages die Kürzel der Bundesländer, in denen er gilt.

Funktion F_BC_Holiday2

Die Funktion ermittelt die Feiertage pro Jahr und pro Bundesland, als Basis dient oben beschriebene Funktion dbo.F_BC_Holiday.

Parameter:

@Year – Kalenderjahr, für welches die Feiertage ermittelt werden soll

@include_weekend – Wochenenden in Ermittlung einschließen = 1 oder nicht = 0

@BundeslandID – Kürzel des gewünschten Bundeslandes

Beispiel:

SELECT * FROM dbo.F_BC_Holiday2 (2024, 0, 'BY')

Ergebnis:

HolidayDate               HolidayDesc

2024-01-01 00:00:00 Neujahrstag

2024-01-06 00:00:00 Heilige Drei Könige (BW, BY, ST)

2024-03-29 00:00:00 Karfreitag

2024-03-31 00:00:00 Ostersonntag

2024-04-01 00:00:00 Ostermontag

2024-05-01 00:00:00 Tag der Arbeit

2024-05-09 00:00:00 Christi Himmelfahrt

2024-05-19 00:00:00 Pfingstsonntag

2024-05-20 00:00:00 Pfingstmontag

2024-05-30 00:00:00 Fronleichnam (BW, BY, HE, NW, RP, SL)

2024-10-03 00:00:00 Tag der deutschen Einheit

2024-11-01 00:00:00 Allerheiligen (BW, BY, NW, RP, SL)

2024-12-25 00:00:00 1. Weihnachtstag

2024-12-26 00:00:00 2. Weihnachtstag

In diesem Beispiel werden die Feiertage für das Bundesland Bayern (BY) und das Jahr 2024 ohne zusätzliche Anzeige der Wochenenden ausgegeben.

Ermitteln der einzelnen Arbeitstage

Auf der Basis dieser Funktionen können wir einen Arbeitstagkalender bzw. eine Datenbanktabelle, die alle Tage als Arbeitstag oder freien Tag markiert, erstellen.

Dafür sind im Anhang zwei Prozeduren für das Befüllen einer Arbeitstagstabelle abgelegt:

P_APP_Fuellen_Arbeitstagskalender und P_APP_Fuellen_Arbeitstagkalender_2

Beide Prozeduren benötigen als Parameter das Jahr und das Bundesland. Die erforderlichen Kürzel der Bundesländer sind in den Prozeduren aufgeführt. In den Prozeduren wird zunächst das Vorhandensein einer Tabelle dbo.T_S_Arbeitstagkalender geprüft und ob bereits Datensätze zu dem angegebenen Jahr und Bundesland existieren, um zu verhindern, dass Datensätze doppelt angelegt werden.

Danach wird die Tabelle dbo.T_S_Arbeitstagkalender angelegt und befüllt oder die bereits vorhandene Tabelle mit den neuen Daten befüllt.

Dafür werden zunächst alle Tage eines Jahres und das jeweilige Bundesland in die Tabelle dbo.T_S_Arbeitstagkalender geschrieben und als Arbeitstag gekennzeichnet. Als Basis hierfür kann die von ETL bereitgestellte View dbo.V_S_Periode dienen (dbo.P_APP_Fuellen_Arbeitstagskalender). Ist diese View nicht verfügbar, kann stattdessen die Prozedur dbo.P_APP_Fuellen_Arbeitstagskalender_2 verwendet werden, in welcher die Tage mittels der SQL-Datumsfunktionen per Rekursion eingefügt werden.

Im Anschluss werden die Feiertage und auch die Wochenenden über die ETL-Funktionen dbo.F_BC_Holiday bzw, dbo.F:BC_Holiday2 neu als Feiertag oder Wochenende markiert.

Wichtig ist: Die Spracheinstellung der Datenbank bzw. des Datenbankservers definiert die IDs, welche den einzelnen Wochentagen zugeordnet sind. In der deutschen Spracheinstellung sind Samstage und Sonntage mit der ID 6 bzw. 7 hinterlegt, in der englischen Spracheinstellung mit ID = 7 für Samstag und ID = 1 für Sonntag. Die jeweils eingestellte Sprache kann mit: SELECT @@LANGUAGE AS ‘Language Name’ auf Datenbankebene ermittelt werden. Die hier beschriebenen Prozeduren gehen bei der Ermittlung der Wochenenden von einer deutschen Spracheinstellung aus.

Verwendet man für die Kennzeichnung neben der Bezeichnung des Feiertages/Wochenendes einen numerischen Schlüssel wie z.B. 0 für kein Arbeitstag und 1 für Arbeitstag, kann pro Woche, Monat oder Jahr die konkrete Anzahl der Arbeitstage für einen Standort anhand seines Bundeslandes durch das Anlegen einer entsprechenden Kennzahl bestimmt werden. Durch die Einbeziehung der Anzahl der Arbeitstage in die Berechnung werden Kennzahlen, die von der Produktivität o. ä. Kennzahlen abhängen, zwischen den Bundesländern besser vergleichbar.

Anhang

P_APP_Fuellen_Arbeitstagskalender.sql

ALTER PROC [dbo].[P_APP_Fuellen_Arbeitstagskalender] (@Jahr int, @Bundesland varchar(50))
AS
BEGIN

-- Ermitteln Anzahl Arbeitstage pro Bundesland ohne Wochenende 
-- Basis für Ermitteln Wochenende (Samstag und Sonntag) und Feiertage = F_BC_Holiday2 und V_S_Periode

-- Kürzel für die Bundesländer:
--	BE - Berlin
--  BB - Brandenburg
--  BW - Baden-Württemberg
--  BY - Bayern
--  HB - Bremen
--  HE - Hessen
--  HH - Hamburg
--  MV - Mecklenburg-Vorpommern
--  NI - Niedersachsen
--  NW - Nordrhein-Westfalen
--  RP - Rheinland-Pfalz
--  SL - Saarland
--  SN - Sachsen
--  ST - Sachsen-Anhalt
--  SH - Schleswig-Holstein
--  TH - Thüringen

-- Aufruf der Prozedur (Beispiel): EXEC dbo.P_APP_Fuellen_Arbeitstagskalender @Jahr = 2023, @Bundesland = 'BY'


	DECLARE @Msg varchar(500)
	, @Beschreibung varchar(100) = 'Arbeitstag'

	IF EXISTS(SELECT * FROM sys.objects so WHERE so.[name] = 'T_S_Arbeitstagkalender' AND so.schema_id = (SELECT sc.schema_id FROM sys.schemas sc WHERE sc.[name] = 'dbo'))
	-- Tabelle existiert
	BEGIN
		-- Prüfen, ob Tage bereits in der Tabelle existieren
		IF EXISTS(SELECT * FROM dbo.T_S_Arbeitstagkalender WHERE YEAR(TagID) = @Jahr AND Bundesland = @Bundesland)
		BEGIN
			SET @Msg = 'Arbeitstage für ' + CAST(@Jahr AS varchar) + ' und Bundesland ' + @Bundesland + ' sind bereits vorhanden!'
			RAISERROR(@Msg,16,1) return
		END
		ELSE
		BEGIN
			INSERT INTO dbo.T_S_Arbeitstagkalender (TagID, Arbeitstag, Beschreibung, Bundesland)
			-- alle Tage in Tabelle T_S_Arbeitstagkalender füllen und zunächst als Arbeitstag kennzeichnen
			SELECT
				vp.TagID
				, 1
				, @Beschreibung
				, @Bundesland
			FROM dbo.V_S_Periode vp
			WHERE vp.JahrID = @Jahr

		END
	END
	ELSE
	BEGIN
		-- Tabelle existiert noch nicht
		-- alle Tage in Tabelle T_S_Arbeitstagkalender füllen und zunächst als Arbeitstag kennzeichnen
		SELECT
			vp.TagID AS TagID
			, 1 AS Arbeitstag
			, @Beschreibung AS Beschreibung
			, @Bundesland AS Bundesland
		INTO dbo.T_S_Arbeitstagkalender
		FROM dbo.V_S_Periode vp
		WHERE vp.JahrID = @Jahr

	END

	-- Kennzeichnen Wochenende und Feiertage als Nicht-Arbeitstage
	UPDATE k
	SET 
		k.Arbeitstag = 0
		, k.Beschreibung = HolidayDesc
	FROM dbo.[F_BC_Holiday2] (@Jahr, 1, @Bundesland)
	INNER JOIN dbo.T_S_Arbeitstagkalender k
		ON k.TagID = CAST(HolidayDate AS date)
END

P_APP_Fuellen_Arbeitstagskalender_2.sql

ALTER PROC [dbo].[P_APP_Fuellen_Arbeitstagskalender_2] (@Jahr int, @Bundesland varchar(50))
AS
BEGIN

-- Ermitteln Anzahl Arbeitstage pro Bundesland ohne Wochenende 
-- Basis für Ermitteln Wochenende (Samstag und Sonntag) -  Rekursion
-- Ermitteln Feiertage je Bundesland - F_BC_Holiday2

-- Kürzel für die Bundesländer:
--	BE - Berlin
--  BB - Brandenburg
--  BW - Baden-Württemberg
--  BY - Bayern
--  HB - Bremen
--  HE - Hessen
--  HH - Hamburg
--  MV - Mecklenburg-Vorpommern
--  NI - Niedersachsen
--  NW - Nordrhein-Westfalen
--  RP - Rheinland-Pfalz
--  SL - Saarland
--  SN - Sachsen
--  ST - Sachsen-Anhalt
--  SH - Schleswig-Holstein
--  TH - Thüringen

-- Aufruf der Prozedur (Beispiel): EXEC dbo.P_APP_Fuellen_Arbeitstagskalender_2 @Jahr = 2023, @Bundesland = 'BY'

	DECLARE @Msg varchar(500)
	, @Startdatum date 
	, @Enddatum date
	, @Beschreibung varchar(100) = 'Arbeitstag'

	SET @Startdatum = CONVERT(date, CAST(@Jahr*10000 + 1 * 100 + 1 AS varchar))
	SET @Enddatum = CONVERT(date, CAST(@Jahr*10000 + 12 * 100 + 31 AS varchar))

	IF EXISTS(SELECT * FROM sys.objects so WHERE so.[name] = 'T_S_Arbeitstagkalender' AND so.schema_id = (SELECT sc.schema_id FROM sys.schemas sc WHERE sc.[name] = 'dbo'))
	-- Tabelle existiert
	BEGIN
		-- Prüfen, ob Tage bereits in der Tabelle existieren
		IF EXISTS(SELECT * FROM dbo.T_S_Arbeitstagkalender WHERE TagID between @Startdatum AND @Enddatum AND Bundesland = @Bundesland)
		BEGIN
			SET @Msg = 'Arbeitstage für ' + CAST(@Jahr AS varchar) + ' und Bundesland ' + @Bundesland + ' sind bereits vorhanden!'
			RAISERROR(@Msg,16,1) return
		END
		ELSE
		BEGIN
			WITH cte_kalender
			AS
			(
				SELECT @Startdatum AS Tag
				UNION ALL
				SELECT DATEADD(DD,1,Tag) FROM cte_kalender
					WHERE DATEADD(DD,1,Tag) <= @Enddatum
			)
			-- Spracheinstellungen auf Datenbankserver/Datenbank berücksichtigen, hier Sprachinstellung = deutsch und damit Samstag = 6 und Sonntag = 7
			INSERT INTO dbo.T_S_Arbeitstagkalender (TagID, Arbeitstag, Beschreibung, Bundesland)
			SELECT 
				Tag
				, CASE WHEN DATEPART(WEEKDAY,Tag) in (6,7)		 
					THEN 0
					ELSE 1
				  END
				, CASE WHEN DATEPART(WEEKDAY,Tag) in (6,7)
					THEN 'Wochenende'
					ELSE 'Arbeitstag'
				  END
				, @Bundesland
			FROM cte_kalender
			OPTION (MAXRECURSION 0)
		END
	END
	ELSE
	BEGIN
		-- Tabelle existiert noch nicht
		WITH cte_kalender
		AS
		(
			SELECT @Startdatum AS Tag
			UNION ALL
			SELECT DATEADD(DD,1,Tag) FROM cte_kalender
				WHERE DATEADD(DD,1,Tag) <= @Enddatum
		)

		SELECT 
			Tag AS TagID
			, CASE WHEN DATEPART(WEEKDAY,Tag) in (6,7)
				THEN 0
				ELSE 1
			  END
			AS Arbeitstag
			, CASE WHEN DATEPART(WEEKDAY,Tag) in (6,7)
				THEN CAST('Wochenende' AS varchar(100))
				ELSE @Beschreibung
			  END 
			AS Beschreibung
			, @Bundesland AS Bundesland
		INTO dbo.T_S_Arbeitstagkalender
		FROM cte_kalender k
		OPTION (MAXRECURSION 0)
	END

	---- Feiertage eintragen
	UPDATE k
	SET 
		k.Arbeitstag = 0
		, k.Beschreibung = HolidayDesc
	FROM dbo.[F_BC_Holiday2] (@Jahr, 0, @Bundesland)
	INNER JOIN dbo.T_S_Arbeitstagkalender k
		ON k.TagID = HolidayDate


END