Datum addieren

Question

Hallo zusammen,
ich muss zu einem Datumswert, +3 Tage dazu addieren. Dabei sollen bei der Addition die Wochenendtage (Samstag, Sonntag) sowie die Feiertage nicht mit gezählt werden. Heißt, Freitag + 3 soll nicht Montag ergeben, sondern Mittwoch. Donnerstag + 3 !=Sonntag sondern Dienstag! Das Startdatum kann dabei ruhig ein Feiertag bzw. ein Wochenendtag sein. Kann mir jemand einen Tipp geben, wie ich das ganze in T-SQL umsetzen kann?

Viele Grüße,
Abgoosht

Answer 1

Hallo Abgoosht, das sollte über CASE gehen.

Declare @MyDate date = '2013-03-04';

Select DATENAME(DW, @MyDate), datepart(WEEKDAY, @MyDate),
case
     when datepart(WEEKDAY, @MyDate) < 4 then dateadd(DAY, 3, @MyDate)
     when datepart(WEEKDAY, @MyDate) > 6 then dateadd(DAY, 3, @MyDate)
     else dateadd(DAY, 7, dateadd(DAY, -2, @MyDate))
end as Result;

Probiere mal die Werte bis zum 10.3.2013 durch.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 2

Geht natürlich auch einfacher!

Declare @MyDate date = '2013-03-04';

Select @MyDate as Input, DATENAME(DW, @MyDate), datepart(WEEKDAY, @MyDate),
case
     when datepart(WEEKDAY, @MyDate) between 4 and 6 then dateadd(DAY, 7,
dateadd(DAY, -2, @MyDate))
     else dateadd(DAY, 3, @MyDate)
end as Result;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 3

Hallo,

hatte es gerade fertig, als in der Zwischenzeit hatte Christoph schon geantwortet.

Ich schicke es mal trotzdem als Ergänzung:

Schöne Grüsse.

DECLARE @DateMax DATETIME, @DateMin DATETIME;
SET @DateMin = '2011-01-01'
SET @DateMax = '2012-31-12';
WITH
 Calendar AS 
 (
  SELECT @DateMin AS DateDay, @DateMin AS Datum_ADD
  UNION ALL
  SELECT DATEADD(DAY, 1, DateDay), DATEADD(DAY, CASE WHEN DATEPART(dw, DateDay) BETWEEN 0 AND 5 THEN 4 ELSE 1 END, DateDay) AS Datum_ADD
  FROM  Calendar
  WHERE DateDay < @DateMax
)
SELECT * FROM CALENDAR
OPTION  (MAXRECURSION 0)

Answer 4

Hallo Christoph,

vielen Dank für Deine Hilfe und für den Code.

Beim testen ist mir aufgefallen, dass die Feiertage mit gezählt werden. Das wollte ich gerne vermeiden. Heißt, der 30.04.2013 + 3 Tage sollte als Ergebnis nicht den 03.05.2013 ausgeben, sondern den 06.05.2013 (Da 01.05. ein Feiertag). Kann man die Feiertage noch irgendwie mit berücksichtigen? Meintewegen auch nur für ein Bundesland?

Danke im Voraus und beste Grüße,
Abgoosht 

Answer 5

Hallo Abgoosht,
dafür musst Du die Feiertage in einem Kalender hinterlegen. Das hatte ich leider im Originalposting überlesen.
Für jeden Feiertag, der in dem Bereich von Input und Result fällt, musst Du noch mal einen Tag drauf addieren. Also genau genommen die Anzahl der Feiertage in dem Intervall ermitteln und drauf addieren.

Schau mal, ob das hier hilft:

Declare @MyDate date = '2013-03-06';

Declare @Feiertage as Table (Datum date);

Insert into @Feiertage(Datum) values ('2013-03-07');
Insert into @Feiertage(Datum) values ('2013-03-08');

With Rohdaten as
(
Select @MyDate as Input, DATENAME(DW, @MyDate) as Wochentag,
datepart(WEEKDAY, @MyDate) as Tag_der_Woche,
case
     when datepart(WEEKDAY, @MyDate) between 4 and 6 then dateadd(DAY, 7,
dateadd(DAY, -2, @MyDate))
     else dateadd(DAY, 3, @MyDate)
end as Result
)
Select r.Input, r.Wochentag, r.Tag_der_Woche, r.Result, x.Korrektur,
dateadd(day, x.Korrektur, r.Result) as Result2
from Rohdaten r,
(Select count(*) as Korrektur from Rohdaten r, @Feiertage f where f.Datum
between r.Input and r.Result) x
;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 6

Hallo Christoph,

danke für Deine Antwort und das noch so spät am Freitag :).

Feiertage werde jetzt zwar ausgeschlossen aber nicht in Kombination mit Wochenenden. Heißt, wenn ich 30.04.2013 eingeben, dann wird als Korrektur der Feiertag 01.05.2013 erkannt, die Wochenenden werden aber jetzt wieder mitgezählt. Wie kann ich die Wochenendtage ebenfalls ausschließen, wenn ein Feiertag erkannt wird? Feiertage UND Wochenenden sollen beim addieren nicht mitgezählt werden.

Danke und beste Grüße,

Abgoosht

Answer 7

Hallo Abgoosht,

lege Dir eine Kalendertabelle an, z. B.:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Dort kannst Du über Bit-Spalten alle möglichen Kriterien, wie Wochenende, Feiertage, Urlaub usw. hinterlegen.
Dann zähle, wie oben von Christoph gezeigt, die Tage, die im Datumsbereich Deinen Anforderungen genügen.

Gruß Elmar

Answer 8

 Hallo zusammen,

irgendwie kriege ich die Anforderung nicht gebacken. Also, ich habe eine Hilfstabelle angelegt, dort habe ich eine Flagspalte, Feiertag_JN eingebaut. Soweit so gut. Beim Addieren werden entweder die Feiertage oder die Wochenenden gezählt. Aber nicht wenn innerhalb eines Zeitraums sowohl ein WE als auch ein Feiertag vorliegt.

Ich habe das Skript von Christoph verwendet. Bitte um Unterstützung.

Viele Grüße,
Abgoosht

Answer 9

Hallo Saleh,
die Bitte um Unterstützung kann ich verstehen. War dann doch nicht ganz so trivial. Ich hoffe, Du verstehst meinen kommentierten Code. Sonst frag einfach nach. Begriffe wie CTE und Window Functions setze ich jetzt mal vorraus

-- Das Datum wollen wir als Startwert für die Berechnung verwenden
Declare @MyDate date = '2013-04-30';

-- Variablen für unseren virtuellen Kalender
DECLARE @startDate datetime,
             @endDate datetime;
 -- 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);
 -- 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
-- Hier werden die Auszeiten schon mal rausgenommen
and not exists (Select *
from @Auszeiten a where dateadd(d, n-1, @startDate) = a.Datum)
;

-- Jetzt wollen wir auf die durchnummerierten Arbeitstage noch einmal drei 
Tage aufaddieren
-- Dazu müssen wir die ROW_NUMBER auch in eine CTE packen
-- Dann können wir einfach auf die durchnummerierten Arbeitstage 3 drauf 
addieren
-- In der Where-Klausel zeigen wir nur die Zeile zu unserem Startwert an
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
),
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
and not exists (Select *
from @Auszeiten a where dateadd(d, n-1, @startDate) = a.Datum)
)
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
;

 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 10

Hier sind noch die ganzen Wochenenden und Feiertage vom Mai 2013:

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);

 Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 11

Hallo Christoph,

vieeeeeeeelen herzlichen Dank für Deine Unterstützung. Es sieht sehr gut aus. Ich hätte noch eine Frage. Wenn ich nun ein Feiertag als StartDatum eingebe, dann wird kein Resultat ausgegeben. An welcher Stelle müsste man eine Änderung vornehmen, wenn auch Feiertage als Startdatum berücksichtigt werden sollen, aber nicht in der Addition der 3 Tage mit gezählt werden sollen?

Nochmals vielen Dank und einen schönen Montag Nachmittag.

Viele Grüße

Answer 12

Hallo Saleh, das musst Du bei der Berücksichtigung der Auszeiten mit einbauen.

...
-- 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)
...

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu/

Answer 13

Hallo Christoph,

besten Dank für Deine Unterstützung. Jetzt sieht es sehr gut aus.

Beste Grüße!

Answer 14

Hallo Christoph,

ich versuche gerade deinen Code in einer Funktion einzubetten, heißt ich möcht eine Funktion bauen mit zwei parameter (StartDatum, AnzahlAddierterTage) und die Funktion soll mit dann StartDatum+AnzahlAddierterTage zurückgeben. Kannst Du mir einen Tipp geben, welche Funktionsart dafür geeignet ist? Mit den CTEs bekomme ich schwierigkeiten. Ich habe das "Multi-statement Table-valued Function" Template dafür genommen. Bin ich damit auf dem richtigen weg?

Danke und beste Grüße,
Saleh

Answer 15

Ich habe ein Stored-Procedure dafür verwendet und es funktioniert einwandfrei.

Danke und beste Grüße,

Saleh