Entwickler-Ecke
Datenbanken - Anzahl Werktage(!) zwischen zwei Daten
Narses - Di 16.08.11 23:45
Titel: Anzahl Werktage(!) zwischen zwei Daten
Moin!
Ich frag einfach mal, vielleicht hat ja einer doch noch die grandiose Idee... :think: ;)
Wie kann man im MySQL-Dialekt die Anzahl der Werktage (konkret: in NRW/DE)
zwischen zwei gegebenen Datumsangaben berechnen? :nixweiss:
Aktueller Stand: Eine Tabelle
calendar (theday DATE, workday TINYINT) anlegen, füllen, die Arbeitstage mit !=0 kennzeichnen (z.B. könnte man ja auch noch halbe Tage differenzieren wollen) und dann ein
SQL-Anweisung
1:
| SELECT COUNT(*) FROM calendar WHERE (theday BETWEEN d1 AND d2) AND (workday != 0) |
absetzen. :?
Andere Vorschläge? :lupe: :gruebel:
cu
Narses
DonManfred - Mi 17.08.11 07:10
 Narses hat folgendes geschrieben  : |
Moin!
Wie kann man im MySQL-Dialekt die Anzahl der Werktage (konkret: in NRW/DE) zwischen zwei gegebenen Datumsangaben berechnen? :nixweiss:
[..]
Andere Vorschläge? :lupe: :gruebel:
|
Ich würds mit
SQL-Anweisung
1:
| SELECT COUNT(*) FROM calendar WHERE (theday BETWEEN d1 AND d2) AND (DAYOFWEEK(theday) BETWEEN 2 AND 6) |
probieren. Obs von der Syntax so passt weiss ich nicht, hab noch keinen Kaffee gehabt :D
Siehe
DayOfWeek [
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek]
Ansonsten:
| Narses hat folgendes geschrieben : |
Moin!
Wie kann man im MySQL-Dialekt die Anzahl der Werktage (konkret: in NRW/DE) zwischen zwei gegebenen Datumsangaben berechnen? :nixweiss: |
Mmmmmhhhhh... Ein Montag ist in NRW, Sachsen, Bayern aber auch in England, den USA oder China ein Montag :D
iKilledKenny - Mi 17.08.11 07:38
Die Frage ist, ob das denn in MySQL sein muss. Ich habe sowas in PHP realisiert, ich speichere ein Anfangsdatum und eine Frist (Anzahl Werktage) in der DB und rechne dann on the fly das Enddatum aus. Das ganze dann Bundesland abhängig, wegen der verschiedenen Feiertage.
DonManfred - Mi 17.08.11 07:46
| iKilledKenny hat folgendes geschrieben : |
| Die Frage ist, ob das denn in MySQL sein muss. Ich habe sowas in PHP realisiert, ich speichere ein Anfangsdatum und eine Frist (Anzahl Werktage) in der DB und rechne dann on the fly das Enddatum aus. Das ganze dann Bundesland abhängig, wegen der verschiedenen Feiertage. |
Narses hat explizit nach einer Lösung in MySql gesucht. Ich gehe also davon aus das er bereits MySQL einsetzt. Und die Lösung ist ja recht einfach; siehe oben. PHP wird ihm da nicht gross weiter helfen da er ja in Delphi programmiert. Auch DA könnte man das sicherlich "berechnen" mit den DATE-routinen...
iKilledKenny - Mi 17.08.11 07:55
| DonManfred hat folgendes geschrieben : |
| Narses hat explizit nach einer Lösung in MySql gesucht. |
Glaub es oder glaub es nicht, aber das habe auch ich gelesen. Deine Lösung mit DayOfWeek berücksichtigt leider nicht die Feiertage, sondern eben nur Montag bis Freitag. Als Feiertage sind die festen und "beweglichen" Feiertage zu berücksichtigen, Stichwort sei hier Ostern.
Außerdem traue ich unserem Admin eine Portierung von PHP nach Delphi durchaus zu.
DonManfred - Mi 17.08.11 08:10
| iKilledKenny hat folgendes geschrieben : |
| Deine Lösung mit DayOfWeek berücksichtigt leider nicht die Feiertage, sondern eben nur Montag bis Freitag. |
Das Narses eine Portierung erstellen könnte nehme ich auch an. ;)
Ansonsten hat er nach Wochentagen gefragt. Ein Montag ist auch am Rosenmontag ein Montag. Und wenn der 1. Weihnachtstag auf einen Montag fällt dann ist auch dieser ein Montag (also ein Wochentag). Ob da nun gearbeitet wird oder nicht (Feiertage) schien mir hier nicht relevant; wurde aber auch nicht explizit angesprochen :D
jasocul - Mi 17.08.11 08:28
Werktage gehen von Montag bis Samstag. Bei Arbeitstagen hängt es vom Gewerbe ab.
Im Gesetz steht meines Wissens, dass Werktage alle Tage sind, die nicht Sonntag heißen oder gesetzliche Feiertage sind.
Somit ist der Einwand von
iKilledKenny auf die Lösung von
DonManfred durchaus berechtigt.
Eine Lösung kann also nur über ein Kennzeichen in der Tabelle oder eine Feiertagstabelle funktionieren, die die entsprechenden Tage markiert und die Berücksichtigung des Sonntags beinhaltet. Ich würde die Lösung mit der Feiertagstabelle bevorzugen.
In Oracle würde ich es so lösen:
SQL-Anweisung
1:
2:
3:
| SELECT COUNT(*) FROM calendar
WHERE (theday BETWEEN d1 AND d2) AND (DAYOFWEEK(theday) BETWEEN 2 AND 7)
AND not exists (select feiertag from feiertagstabelle where theday = feiertag) |
Geht sicher auch mit einem
not .. in, aber das ist Geschmackssache.
In mySQL wird es sicher ein ähnliches Konstrukt geben.
DonManfred - Mi 17.08.11 08:52
| jasocul hat folgendes geschrieben : |
| Werktage gehen von Montag bis Samstag. |
Kommt auf die "Anwendung" an. Im Mietrecht z.b. ist ein Werktag Mo-Fr.
Ansonsten muss ich dir - nach Googlen - Recht geben und meine Lösung von
SQL-Anweisung
1:
| SELECT COUNT(*) FROM calendar WHERE (theday BETWEEN d1 AND d2) AND (DAYOFWEEK(theday) BETWEEN 2 AND 6) |
auf
SQL-Anweisung
1:
| SELECT COUNT(*) FROM calendar WHERE (theday BETWEEN d1 AND d2) AND (DAYOFWEEK(theday) BETWEEN 2 AND 7) |
korrigieren. ;-)
iKilledKenny - Mi 17.08.11 09:06
Das ein Wochen-Werktag (Mo-Sa) auch ein Feiertag sein kann, scheint immer noch nicht angekommen zu sein... 8)
jasocul - Mi 17.08.11 09:07
Das Mietrecht macht da tatsächlich eine Ausnahme :wink:
Trotzdem gehören gesetzliche Feiertage nicht zu den Werktagen und müssen ebenfalls berücksichtigt werden.
jasocul - Mi 17.08.11 09:29
Jetzt werde ich mal kleinkariert. :twisted:
Die Frage bezog sich auf Werktage und nicht auf Arbeitstage. Somit muss die Berücksichtigung von gesetzlichen Feiertagen per Definition von "Werktag" enthalten sein. :wink:
Allerdings heißt das Datenbankfeld "workday", was wiederum mit Arbeitstag übersetzt werden müsste.
Man
Narses! Kannst du deine Fragen nicht besser formulieren? :motz: :mahn:
Narses - Mi 17.08.11 09:33
Moin!
Also erstmal vielen Dank euch allen! :beer:
Ich präzisiere:
- Ich meine mit Werktage => Arbeitstage (MO-FR ohne Feiertage in NRW/DE). :idea:
- Ja, es muss reines MySQL sein, PHP bringt mich hier nicht weiter. :nixweiss:
- Es scheint tatsächlich auf einen Tabellen-Lookup hinauszulaufen (wegen der beweglichen Feiertage), oder? :?
cu
Narses
jasocul - Mi 17.08.11 09:40
Sieht so aus. Alternativ müsstest du bei jedem Eintrag in deiner Tabelle ein entsprechendes Kennzeichen setzen. Damit verlagerst du die Arbeit aber nur, weil du dann ja beim Erfassen des Tages in der Feiertagstabelle nachsehen musst.
Es ist dann wohl eher die Frage, wo es aus Performance-Gründen besser eingebaut wird.
Singlepin - Mi 17.08.11 13:11
Der eleganteste Weg ist wohl nur mit Stored Procedures zu realisieren.
Narses - Mi 17.08.11 17:11
Moin!
| Singlepin hat folgendes geschrieben : |
| Der eleganteste Weg ist wohl nur mit Stored Procedures zu realisieren. |
Na dann, ich bitte um Erleuchtung (=Codebeispiel für den Anwendungsfall, wohlgemerkt: bewegliche Feiertage!). :idea: ;)
cu
Narses
Singlepin - Mi 17.08.11 18:43
Auf die Schnelle.
Die Prozedure könnte so aussehen:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
| -- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wochentage`(
IN
von Date,
bis Date
)
BEGIN
DECLARE i INTEGER;
DECLARE Anzahl INTEGER;
DECLARE Datum DATE;
SET I=0;
SET Anzahl=0;
SET Datum=von;
WHILE(i<=DATEDIFF(bis,von))DO
BEGIN
if DAYOFWEEK(Datum) BETWEEN 2 AND 6 THEN
BEGIN
-- hier müßen wir noch auf Feiertage testen.
SET Anzahl=Anzahl+1;
END;
END IF;
SET Datum=DATE(ADDDATE(Datum, INTERVAL 1 DAY));
SET i=i+1;
END;
END WHILE;
SELECT Anzahl;
END |
Die Pocedure wird so aufgerufen:
SQL-Anweisung
1:
| CALL DB.wochentage("2011-01-14","2011-02-07"); |
Da wo jetzt der Kommentar steht müssen wir nur noch auf Feiertage prüfen.
Bei Festen ist das ja kein Problem.
Bei Beweglichen berechnen wir Ostersonntag und dann den Rest.
Mach ich Morgen fertig.
Ich hoffe es ist das was gebraucht wird.
Moderiert von Narses: SQL-Tags hinzugefügt
Singlepin - Do 18.08.11 08:19
So nun wollen wir mal die Sache zum Abschluß bringen.
Wir brauchen Ostersonntag, dazu hatte ich mal folgende Formel gefunden:
J = 2011
d = REST(REST(J;19)*19+24;30)
dkorr = d-(d>27)
WoTag = REST(GANZZAHL(J*5/4+dkorr+1);7)
Ostern = DATUM(J;3;28)+dkorr-WoTag
mit MySQL sieht das so aus:
SQL-Anweisung
1:
2:
| SET @B40=2011;
SELECT DATE(ADDDATE(CONCAT(@B40,"-03-28"), INTERVAL ((((@B40%19)*19+24)%30)-((((@B40%19)*19+24)%30)>27)-((FLOOR(@B40*5/4+((((@B40%19)*19+24)%30)-((((@B40%19)*19+24)%30)>27))+1))%7)) DAY)); |
Die übrigen beweglichen Feiertage berechnen sich so:
Weiber Fastnacht Ostern - 52
Rosenmontag Ostern - 48
Aschermittwoch Ostern - 46
Karfreitag Ostern - 2
Ostersonntag Ostern
Ostermontag Ostern + 1
Christi Himmelfahrt Ostern + 39
Pfingstsonntag Ostern + 49
Pfingstmontag Ostern + 50
Fronleichnam Ostern + 60
Dann müssen wir meine Precedure nur noch erweiter mit:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
| DECLARE Jahr INTEGER;
DECLARE Ostern DATE;
...
SET Jahr=YEAR(Datum);
SET Ostern=DATE(ADDDATE(CONCAT(Jahr,"-03-28"), INTERVAL ((((Jahr%19)*19+24)%30)-((((Jahr%19)*19+24)%30)>27)-((FLOOR(Jahr*5/4+((((Jahr%19)*19+24)%30)-((((Jahr%19)*19+24)%30)>27))+1))%7)) DAY));
-- if Datum=DATE(ADDDATE(Ostern, INTERVAL -52 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Weiberfastnacht
-- if Datum=DATE(ADDDATE(Ostern, INTERVAL -48 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Rosenmontag
-- if Datum=DATE(ADDDATE(Ostern, INTERVAL -46 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Aschermittwoch
if Datum=DATE(ADDDATE(Ostern, INTERVAL -2 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Karfreitag
if Datum=DATE(ADDDATE(Ostern, INTERVAL 1 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Ostermontag
if Datum=DATE(ADDDATE(Ostern, INTERVAL 39 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Christi Himmelfahrt
-- if Datum=DATE(ADDDATE(Ostern, INTERVAL 49 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Pfingstsonntag
if Datum=DATE(ADDDATE(Ostern, INTERVAL 50 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Pfingstmontag
if Datum=DATE(ADDDATE(Ostern, INTERVAL 60 DAY)) then SET Anzahl=Anzahl-1;END IF; -- Fronleichnam
-- hier müßen wir noch auf festen Feiertage testen.
if Datum=CONCAT(Jahr,'-01-01') then SET Anzahl=Anzahl-1;END IF; -- Neujahr
-- if Datum=CONCAT(Jahr,'-01-06') then SET Anzahl=Anzahl-1;END IF; -- Heilige Drei Könige
if Datum=CONCAT(Jahr,'-05-01') then SET Anzahl=Anzahl-1;END IF; -- Maifeiertag
-- if Datum=CONCAT(Jahr,'-08-15') then SET Anzahl=Anzahl-1;END IF; -- MariÄ Himmelfahrt
if Datum=CONCAT(Jahr,'-10-03') then SET Anzahl=Anzahl-1;END IF; -- Tag der deutschen Einheit
-- if Datum=CONCAT(Jahr,'-10-31') then SET Anzahl=Anzahl-1;END IF; -- Reformationstag
if Datum=CONCAT(Jahr,'-11-01') then SET Anzahl=Anzahl-1;END IF; -- Allerheiligen
-- Buß-und Bettag letzter Mitwoch vor dem 23.November
if Datum=CONCAT(Jahr,'-12-25') then SET Anzahl=Anzahl-1;END IF; -- 1. Weihnachtstag
if Datum=CONCAT(Jahr,'-12-26') then SET Anzahl=Anzahl-1;END IF; -- 2. Weinachttstag
... |
Die ständige Berechnung von Ostersonntag kann man noch einschränken, in dem man das nur beim Jahreswechsel macht. usw.
Narses - Do 18.08.11 11:21
Moin!
Interessanter Ansatz, vielen Dank! :zustimm: Muss ich mir nochmal genauer ansehen. :lupe:
Allerdings habe ich da noch ein praktisches Problem. Ich zeige mal meinen Code, wo diese Differenz ermittelt wird (aktuell noch mit Kalendertagen):
SQL-Anweisung
1:
| SELECT irgendwas... , DATEDIFF(CURDATE(), statechg) AS daycnt FROM irgendwo ... |
Es geht also darum das "daycnt" auf Werktage umzustellen. :idea:
Wenn ich mich recht erinnere, dann liefert eine SP aber ein Result-Set (genau genommen sogar zwei, hinterher nämlich noch ein leeres als Ende-Marker). Wie soll ich das mit dem Select-Statement oben zusammen bringen? :? :nixweiss:
der aufmerksame Leser merkt schon, da hat einer noch nicht ganz so viel mit SPs gemacht... *schäm*
Generelle Überlegung: wie immer bei diesen Problemen ist das Produkt aus Rechenzeit und Speicherplatzbedarf konstant, man ändert den einen Faktor nur auf Kosten des anderen. Damit stellt sich die Frage: wann ist was effizienter? :gruebel:
cu
Narses
Singlepin - Do 18.08.11 12:51
Hallo Narses,
eine SP muß nicht zwingend ein Result-Set haben.
In meiner SP die Select-Anweisung durch SET @meineVariable ersetzten.
Die SP aufrufen und anschließend SELECT @meineVariable; könnte vieleicht ein Weg sein.
zu deiner generellen Überlegung.
Ich ziehe eine Lösung die durch eine komplexere Berechnung mein Problem löst vor.
- kein weiterer Pflegeaufwand bis der Gesetzgeber was ändert.
- keine Eingabefehler
- die Funktionsgrenzen sind theoretisch unendlich, alternativ braut man unendlich Speicherplatz
- schnell änderbar, falls man später noch weiß was man damals gemacht hat.
Natürlich abgesehen von den Fällen wo die Rechenzeit nicht mehr akzeptabel ist.
Entwickler-Ecke.de based on phpBB
Copyright 2002 - 2011 by Tino Teuber, Copyright 2011 - 2025 by Christian Stelzmann Alle Rechte vorbehalten.
Alle Beiträge stammen von dritten Personen und dürfen geltendes Recht nicht verletzen.
Entwickler-Ecke und die zugehörigen Webseiten distanzieren sich ausdrücklich von Fremdinhalten jeglicher Art!