Wochentage zuverlässig abfragen

Question

Hallo Experts,

um Reports zeitgesteuert zu liefern, muss ich Wochentage zählen. Ich Abhängigkeit von dem Ereignisdatum sollen Reports 3, 2 oder 1 Tage vor dem Ereignis verschickt werden. Wenn Sa oder So dazwischen liegen, sollen die Reports vorher da sein, weil am Sa oder So niemand diese lesen würde. So bin ich dabei ein Regelwerk zu schaffen, welche dann universal verwendet werden kann. Mein Problem ist ja nur dass die Sowohl die Wochentage als auch DATEPARTS irgendwie falsch ermittelt werden und @i anstatt = 0 wird z.B =1 gesetzt (z. B der Ereignis am 01.04.2016 soll 1 Tag vorher gemeldet werden, am 31.03.2016. Meine Prozedur (s. unten) gibt aber den 30.03.2016). Wie kann ich diese Zeitsteuerung zuverlässig automatisieren? Mir ist nicht anderes eingefallen als die drunter stehende Ergebnis mit einer Schleife täglich durchzulaufen, welche den "Leferungstag" = "heute" sucht

Und what's my Wrong???!!! :(((

Vielen lieben Dank vorab und viele Grüße

Irina

declare @i int
select @i =
case DATENAME(dw, d.datum)
when 'Sunday' then 1
when 'Monday' then 2
when 'Tuesday' then 2
when 'Wednesday' then 4
when 'Thursday' then 0
when 'Friday' then 0
when 'Saturday' then 0
end
FROM (SELECT datum as Datum from dbo.EREIGNISTABELLE)AS d

declare @Meldetage table (
ereignisdatum date
, ereignistag varchar(20)
, Vor_drei_Tagen_Datum date
, Vor_drei_Tagen_Wochentag varchar(20)
, Vor_zwei_Tagen_Datum date
, Vor_zwei_Tagen_Wochentag varchar(20)
, Vor_ein_Tagen_Datum date
, Vor_ein_Tagen_Wochentag varchar(20)
)

insert into @Meldetage
select d.datum as ereignisdatum
, DATENAME(dw, d.datum) AS ereignistag
, DATEADD(day, (-3-@i), d.datum) as Vor_drei_Tagen_Datum
, datename(dw,DATEADD(day, (-3-@i), d.datum)) as Vor_drei_Tagen_Wochentag
, DATEADD(day, (-2-@i), d.datum) Vor_zwei_Tagen_Datum
, datename(dw,DATEADD(day, (-2-@i), d.datum)) as Vor_zwei_Tagen_Wochentag
, DATEADD(day, (-1-@i), d.datum) as Vor_ein_Tagen_Datum
, datename(dw,DATEADD(day, (-1-@i), d.datum)) Vor_ein_Tagen_Wochentag
from dbo.EREIGNISTABELLE d

select * from @Meldetage
where ereignisdatum >= getdate()
order by ereignisdatum desc

---

Irina

Answer 1

z. B der Ereignis am 01.04.2016 soll 1 Tag vorher gemeldet werden, am 31.03.2016. Meine Prozedur (s. unten) gibt aber den 30.03.2016)

.....

select * from @Meldetage
where ereignisdatum >= getdate()
order by ereignisdatum desc

Hallo Irina,

ohne Beispieldaten ist es schwer nachzuvollziehen. Ins blaue geraten würde ich sagen, es liegt am GetDate und dem Umstand, das es auch die aktuelle Uhrzeit beinhaltet. Ein heutiges Datum erhältst Du so nicht, allerdings dem DESC Sortierung nach, soll das wohl auch nicht geliefert werden?

Wie gesagt, Beispieldaten aus EREIGNISTABELLE (als INSERT's) und das erwartete Ergebnis währen hier hilfreich.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Olaf, toll dass Du geantwortet hast, denn ich habe das ganze anhand Deiner Statement "Ist dann und wan Wochenende oder muss ich arbeiten" gemacht. Allerdings habe ich festgestellt dass man kann sich absolut nicht verlassen dass der datepart So immer 1 ist - das ist irgendwie Kalenderjahre abhängig...

Aus der ereignistabelle benutze ich datum, diese Datum komt von dem IBE:

CREATE TABLE [dbo].[ereignise](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FI_DATUM_ID] [int] NULL,
    [DATUM] [datetime] NULL,
    [Ereinis_Nr] [varchar](10) NULL
) ON [PRIMARY];

insert into [dbo].[ereignise]( datum)
select '2016-05-20 00:00:00.000'
union all
select'2016-09-20 00:00:00.000'
union all
select'2016-09-30 00:00:00.000'
union all
select'2016-10-10 00:00:00.000'
union all
select'2016-10-20 00:00:00.000'
union all
select'2016-10-30 00:00:00.000'
union all
select'2016-11-13 00:00:00.000'
union all
select'2016-11-20 00:00:00.000'
union all
select'2016-11-27 00:00:00.000'
union all
select'2016-12-04 00:00:00.000'
union all
select'2016-12-11 00:00:00.000'
union all
select'2016-12-18 00:00:00.000'
union all
select'2016-12-25 00:00:00.000'
union all
select'2010-01-01 00:00:00.000'
union all
select'2010-01-08 00:00:00.000'

---

Irina

Answer 3

...die DESC Sortierung ist nur da um den höchsten Datum oben anzuzeigen - das ist lediglich bequemlichkeitshalber, kan man auch wegdenken Datum >= getdate ist ebenso nur um die Ergebnise einzuschränken damit es schneller läuft.

Als Ergebnis möchte ich eine Auflistung aller Ereignisdartumer mit dazugehörigen Lieferterminen, manche Ereignise müssen 3 Tage vorher, andere 2 und 1 Tage vorher gemeldet werden an die Fachabteilungen. Es soll berücksichtigt werden dass am Sa und So niemend von der fachabteilung da ist um die gelieferten Daten zu lesen.

Dann möchte ich um die Liste der Datümer und Liefertermine eine Schleife bauen, welche nach dem Liefertermin = "heute" sucht und REportjob anstösst.Auf jeden Fall sind meine Liefertage falsch berechnet

Flugdatum    Flugtag    Vor_drei_Tagen_Datum        Vor_drei_Tagen_Wochentag    Vor_zwei_Tagen_Datum    Vor_zwei_Tagen_Wochentag    Vor_ein_Tagen_Datum    Vor_ein_Tagen_Wochentag
2016-04-16    Saturday    2016-04-12    Tuesday        2016-04-13    Wednesday    2016-04-14    Thursday
2016-04-07    Thursday    2016-04-03    Sunday        2016-04-04    Monday        2016-04-05    Tuesday
2016-04-05    Tuesday        2016-04-01    Friday        2016-04-02    Saturday    2016-04-03    Sunday
2016-04-03    Sunday        2016-03-30    Wednesday    2016-03-31    Thursday    2016-04-01    Friday
2016-04-02    Saturday    2016-03-29    Tuesday        2016-03-30    Wednesday    2016-03-31    Thursday
2016-04-01    Friday        2016-03-28    Monday        2016-03-29    Tuesday        2016-03-30    Wednesday
2016-03-26    Saturday    2016-03-22    Tuesday        016-03-23    Wednesday    2016-03-24    Thursday

---

Irina

Answer 4

 Allerdings habe ich festgestellt dass man kann sich absolut nicht verlassen dass der datepart So immer 1 ist - das ist irgendwie Kalenderjahre abhängig...

Hallo Irina,

vom Jahr ist es nicht abhängig, aber von den Sessioneinstellungen. In den USA beginnt die Woche bereits am Sontag, bei uns am Montag und darauf basiert nun mal der "Day of Week".

Das hier

SET LANGUAGE DEUTSCH;
SELECT DATEPART(dw, {d N'2014-07-06'})
GO
SET LANGUAGE ENGLISH;
SELECT DATEPART(dw, {d N'2014-07-06'})
GO
SET DATEFIRST 4
SELECT DATEPART(dw, {d N'2014-07-06'})

liefert 3 verschiedene Ergebnisse

Mit den Testdaten und Deiner Abfrage kommt bei mir (und LANGUAGE ENGLISH) das hier bei raus und da kann ich keinen Fehler sehen.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Das stimmt, bei Dir sieht das richtig aus.

Aber was ist nur mit mir? Bin ich blind? Warum ist @i immer 1, egal welche Tag das ist?

SET LANGUAGE ENGLISH;

declare @i int
select @i =
case datepart(dw, d.datum)
when 1 then 1
when 2 then 2
when 3 then 2
when 4 then 4
when 5 then 0
when 6 then 0
when 7 then 0
end
FROM (SELECT datum as Datum from dbo.ereignise)AS d


select @i as I,DATEADD(day, (-1-@i), d.datum) as Ein_Tag_vorher_Datum
,datename(dw,DATEADD(day, (-1-@i), d.datum)) Ein_Tag_vorher_Wochentag
, DATENAME(dw, d.datum) AS ereignistag, d.datum, datepart(dw, d.datum)
from dbo.ereignise d
where d.datum = '2014-07-21 00:00:00.000'

---

Irina

Answer 6

declare @i int
select @i =
case datepart(dw, d.datum)
when 1 then 1
when 2 then 2
when 3 then 2
when 4 then 4
when 5 then 0
when 6 then 0
when 7 then 0
end
FROM (SELECT datum as Datum from dbo.ereignise)AS d

Weil die Abfrage Dir irgend eine Wert liefert. Du fragst die Tabelle Ereignise ungefiltert ab und weisst das Ergebnis einer skalaren Variable zu und die hat zum Schluß den letzten Wert der Abfrage / CASE Statement und da es auch keine Sortierung gibt, ist die ein eher zufälliger Wert.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Hallo Olaf,

daran wird's wohl liegen. Wie kann ich das am besten lösen? Wie kann ich "Select @i" am besten an den anderen Statement anhängen? Damit die gesamte Prozedur nicht all so langer läuft?

Herzlichen Dank nochmals für Deine Beteiligung

Viele Grüße

Irina

---

Irina

Answer 8

Folgendes ist mir eingefallen (s.unten). Ich frage mich bloß ob es ein effizienter und üblicher Weg wäre einen Timetable zum Verschicken von Reports (daten gesteuert, in Anlehnung an http://www.insidesql.org/blogs/cmu/sql_server/datengesteuerte-abonnements-mit-der-standard-edition) wäre. Könnte man ewtl einfacher machen?

Ergenzung: ich werde nun eine Schleife drum rum bauen um täglich zu überprüfen ob den Datum nach heute irgendwelche Ereignise an die Fachabteilungen gemeldet werden sollen...

SET LANGUAGE ENGLISH;

declare @Meldetage table (
ereignisdatum date
, ereignistag varchar(20)
, Vor_drei_Tagen_Datum date
, Vor_drei_Tagen_Wochentag varchar(20)
, Vor_zwei_Tagen_Datum date
, Vor_zwei_Tagen_Wochentag varchar(20)
, Vor_ein_Tagen_Datum date
, Vor_ein_Tagen_Wochentag varchar(20)
)


declare @i table (i int, datum date)
insert
into @i  
select case datepart(dw, d.datum)
when 1 then 1
when 2 then 2
when 3 then 2
when 4 then 4
when 5 then 0
when 6 then 0
when 7 then 0
end, d.datum
FROM (SELECT datum as Datum from dbo.ereignise)AS d

insert into @Meldetage
select s.datum as ereignisdatum
, DATENAME(dw, s.datum) AS ereignistag
, DATEADD(day, (-3-i), s.datum) as Vor_drei_Tagen_Datum
, datename(dw,DATEADD(day, (-3-i), s.datum)) as Vor_drei_Tagen_Wochentag
, DATEADD(day, (-2-i), s.datum) Vor_zwei_Tagen_Datum
, datename(dw,DATEADD(day, (-2-i), s.datum)) as Vor_zwei_Tagen_Wochentag
, DATEADD(day, (-1-i), s.datum) as Vor_ein_Tagen_Datum
, datename(dw,DATEADD(day, (-1-i), s.datum)) Vor_ein_Tagen_Wochentag
from dbo.ereignise s

cross apply (select i from @i i where s.datum = i.datum) i



select distinct * from @Meldetage
where ereignisdatum >= getdate()
order by ereignisdatum desc

---

Irina

Answer 9

Hallo Irina,

dein aktueller Ansatz hat m.E. zwei Schwachstellen:

1) die "Ausnahme" bezieht sich nur auf Wochenenden.

Aber was ist mit Feiertagen (Ostern, Weihnachten, 3.Oktober, ...), Brückentagen (z.B. 2. Mai, Tag nach Himmelfahrt) und Betriebsruhe (z.B. zw. Weihnachten und Neujahr)?

Für die von dir geschilderte Aufgabe empfehle ich üblicherweise die Verwendung einer Kalender-Tabelle, in der alle Kalendertage des relevanten Zeitraums gelistet sind (Datum [DATE]) und zusätzlich eine Spalte (IstArbeitstag [tinyint]), aus der erkennbar ist, ob es sich um einen Arbeitstag handelt (0=nein, 1=ja).

Den nächsten Arbeitstag zu ermitteln, ist dann ziemlich einfach:

SELECT TOP 1 Datum
FROM Kalender
WHERE Datum > GETDATE()
AND IstArbeitstag = 1
ORDER BY Datum

und nun zur zweiten Schwachstelle:

DATENAME und DATEPART sind nicht deterministisch, d.h. sie sind abhängig von den Werten für SET LANGUAGE, SET DATEFORMAT und SET DATEFIRST zum Zeitpunkt der Ausführung der Anweisung. Ein user mit der Default-Sprache DEUTSCH erhält ein anderes Ergebnis als ein user mit Default-Sprache ENGLISH. Das Ganze kann dann noch innerhalb der Session (z.B. innerhalb einer übergeordneten Prozedur) verändert werden. 

Für die Ermittlung des Wochentages empfehle ich einen Code, der völlig unabhängig von den Datums-relevanten Einstellungen immer das gleiche Ergebnis liefert:

SELECT 1 + DATEDIFF(dd,0,Datum)%7 
FROM dbo.ereignise

Wie funktioniert's?

DATEDIFF(dd,0,Datum) gibt die Anzahl der Tage seit dem 1.1.1900 zurück. "Rein zufällig" war dies ein Montag :-)

Über %7 wird der Rest der Division durch 7 ermittelt. Dies entspricht der Anzahl der Tage, die seit dem letzten Montag vergangen sind (0.. 6).

Jetzt noch +1 und wir haben die in Dt. gültige Numerierung der Wochentage (Mo. = 1 bis So. = 7). Egal, welche Server- User- oder Sessioneinstellungen verwendet werden.