« Microsoft BI Authentication and Identity Delegation
SSDT BI for Visual Studio 2012 »

Einige Arbeitstage in der Zukunft

Und schon wieder ein Artikel basierend auf einer Frage aus dem SQL Server Forum, welches für eine Inspiration immer gut ist.
Aufgabenstellung: Ausgehend von einem Startwert soll das Datum des Tages berechnet werden, dass drei Arbeitstage (unter Berücksichtigung der Feiertage und Wochenenden) in der Zukunft liegt.

Nachdem endlich alle Anforderungen zusammen waren, konnte eine schlanke Lösung entwickelt werden, die unter anderem eine von Elmar Boye vorgeschlagene Tabelle mit den Auszeiten (Feiertage, Wochenenden, ...) und entsprechenden Flags berücksichtigt. Wenn man sich einen solchen Firmenkalender aufbauen kann, ist die Planung der zukünftigen Arbeitstage etwas einfacher. Zur Kennzeichnung der verschiedenen Arten von Auszeiten kommen hier einzelne Bits ins Spiel, die aber in dieser Lösung keine Rolle spielen, da wir alle Auszeiten auslassen wollen.

Auszeiten definieren

Wie bereits oben angedeutet, kommt hier eine Tabelle mit den Auszeiten ins Spiel. Für dieses Beispiel ist es aber lediglich eine Tabellenvariable.

-- An diesen Tagen wird nicht gearbeitet
DECLARE @Auszeiten AS TABLE (Datum date, Feiertag bit, Wochenende bit);

INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-01', 1, 0);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-04', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-05', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-09', 1, 0);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-11', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-12', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-18', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-19', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-20', 1, 0);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-25', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-26', 0, 1);
INSERT INTO @Auszeiten(Datum, Feiertag, Wochenende) VALUES ('2013-05-30', 1, 0);

Die Lösung mit einer Window Function

An dieser Stelle greifen wir auf eine Window Function zurück, die uns seit der Version SQL Server 2008 zur Verfügung steht.

Damit wir einige Tage in die Zukunft rechnen können, müssen wir die möglichen Arbeitstage ermitteln und diese dann einfach durchnummerieren. Das geschieht mit der ROW_NUMBER() Funktion. Aber wie kommen wir denn an einen Kalender?

Kalender selber bauen

Hierzu verwenden wir eine Lösung, die ich einmal bei Steve Kass gesehen habe. Er hat sich über einige CTEs, die aufeinander aufbauen, mittels CROSS JOINs eine ausreichende Menge an Zeilen (alle mit dem Wert 1) aufgebaut. Die letzte CTE nummeriert nun diese Sätze auch wieder mit der ROW_NUMBER() Funktion durch und erhält so eine ganze Reihe von aufsteigenden lückenlosen Nummern. Diese Nummern werden mittels DateAdd() Funktion zum Startwert hinzuaddiert und man erhält so viele Tage ab dem Startwert, wie zwischen Start- und Endwert liegen.

-- Variablen für unseren virtuellen Kalender
DECLARE @startDate DATETIME,
       @endDate DATETIME;

-- Das sind die Start und Endewerte für unseren Kalender der nächsten 14 Tage
SET @startDate  = '2013-04-30';
SET @endDate    = DATEADD(DAY, 14, @StartDate);

-- Diese CTEs liefern eine Tabelle mit n Zeilen, die zum Aufbau eines Kalenders verwendet werden können
-- Die eigentliche Verarbeitung kommt dann im nächsten Statement
WITH
t0(n) AS
(
SELECT 1
UNION ALL
SELECT 1
),
t1(n) AS
(
SELECT 1
FROM t0 AS a
CROSS JOIN t0 AS b
),
t2(n) AS
(
SELECT 1
FROM t1 AS a
CROSS JOIN t1 AS b
),
Numbers(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
FROM t2
)
SELECT ROW_NUMBER() OVER (ORDER BY n) AS Arbeitstage, DATEADD(d, n-1, @startDate) AS Datum
FROM Numbers
WHERE n <= DATEDIFF(d, @startDate, @endDate) + 1;

Kalendertage durchnummerieren

Nachdem nun also ein Kalender zur Verfügung steht, kann man auch diese Tage durchnummerieren. Natürlich mit ROW_NUMBER(). Anschliessend führen wir einen Self Join mit dem Kalender durch, wobei der zweite Kalender einen Versatz von 3 Tagen erhalten soll. Zu jedem Datum aus dem Kalender erhalten wir dann das Datum des Arbeitstages, welches 3 Arbeitstage später ist. Der Versatz geht einfach über einen Join mit der Nummer, die uns ROW_NUMBER() liefert und dem entsprechenden Gegenstück aus der zweiten Tabelle, welches 3 größer ist.

WITH
... ,
Numbers(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
FROM t2
),
Kalender AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) AS Arbeitstage, DATEADD(d, n-1, @startDate) AS Datum
FROM Numbers
WHERE n <= DATEDIFF(d, @startDate, @endDate) + 1
-- Hier werden die Auszeiten schon mal rausgenommen
-- Falls der Starttag ebenfalls eine Auszeit war, muss er trotzdem rein
AND NOT EXISTS (SELECT *
FROM @Auszeiten a WHERE DATEADD(d, n-1, @startDate) = a.Datum AND a.Datum <> @startDate)
)
SELECT k1.Datum AS Input, k2.Datum AS Result
FROM Kalender k1
INNER JOIN  Kalender k2
ON k1.Arbeitstage + 3 = k2.Arbeitstage
WHERE k1.Datum = @MyDate;

Damit hier die Auszeiten berücksichtigt werden, bekommt bereits die Kalender-CTE eine NOT EXISTS Klausel, die gegen unsere vorher definierten Auszeiten eine Prüfung vornimmt. Falls auch der Startwert in diesen Auszeiten definiert war, muss dies ignoriert werden, da wir ansonsten kein Ergebnis erhalten.

Im Anhang findet man das komplette Skript mit den Beispieldaten für den Mai 2013.

  Kalender mit Feiertage.sql

entwicklung, t-sql, window functions
SQL Server
 Print article This entry was posted by cmu on 12.03.13 at 14:57:00 . Follow any responses to this post through RSS 2.0.