Hey
Ich bräuchte mal einen Denkanstoß:
Ich habe eine Abfrage, die mir die Anzahl der Arbeitstage zwischen zwei Daten zurückgibt.
Arbeitstage: Wenn([Eintritt]>[DatumVon] Und [Eintritt]<[DatumBis];
DatDiff("t";[Eintritt];[DatumBis])-DatDiff("ww";[Eintritt];[DatumBis])*2+1+(Wochentag([Eintritt])=1)+(Wochentag([DatumBis])=7);
Wenn([Austrittd]>[DatumVon] Und [Austrittd]<[DatumBis];
DatDiff("t";[DatumVon];[Austrittd])-DatDiff("ww";[DatumVon];[Austrittd])*2+1+(Wochentag([DatumVon])=1)+(Wochentag([Austrittd])=7);
DatDiff("t";[DatumVon];[DatumBis])-DatDiff("ww";[DatumVon];[DatumBis])*2+1+(Wochentag([DatumVon])
=1)+(Wochentag([DatumBis])=7)))Schwierigkeit: Das Ein- und Austrittsdatum.
Die Tage vor dem Eintritt und nach dem Austritt sollen in dem Zeitraum für den Datensatz nicht mit berechnet werden.
Mit meiner bisherigen Formel klappt es schon fast.
Zum besseren Verständnis: Es gibt 6 Fälle:
1. Eintritt<Anfang<Ende<Austritt
2. Eintritt<Anfang<Austritt<Ende
3. Eintritt<Austritt<Anfang<Ende
4. Anfang<Eintritt<Ende<Austritt
5. Anfang<Eintritt<Austritt<Ende
6. Anfang<Ende<Eintritt<Austritt
Bis auf Fall 5 klappt es, nur ich finde für mein Problem keine Lösung. Unter Google findet sich für "Abrfage zwischen zwei Daten" nur unzutreffendes.
Nur, wenn das Anfangsdatum kleiner und das Enddatum größer als Eintritt und Austritt sind, kommt der Fehler vor.
Hat jemand nen Tipp, wie ich das richtig schreibe? ;)
Danke!
Einmal kurz vom Thema abgelenkt und man hat die Idee ;D Nur leider ist der Code zu lang!!!
Ich habe für den 5. Fall folgenden Code ergänzt:
Wenn ([DatumVon]<[Eintritt] und [DatumBis]<[Austrittd];(DatDiff("t";[DatumVon];[Eintritt])-DatDiff("ww";[DatumVon];[Eintritt])*2+1+(Wochentag([DatumVon])=1)+(Wochentag([Eintritt])=7)))+ DatDiff("t";[DatumVon];[DatumBis])-DatDiff("ww";[Austrittd];[DatumBis])*2+1+(Wochentag([Austrittd])=1)+(Wochentag([DatumBis])=7))));
Gesamtergebnis:Arbeitstage: Wenn ([DatumVon]<[Eintritt] und [DatumBis]<[Austrittd];(DatDiff("t";[DatumVon];[Eintritt])-DatDiff("ww";[DatumVon];[Eintritt])*2+1+(Wochentag([DatumVon])=1)+(Wochentag([Eintritt])=7)))+ DatDiff("t";[DatumVon];[DatumBis])-DatDiff("ww";[Austrittd];[DatumBis])*2+1+(Wochentag([Austrittd])=1)+(Wochentag([DatumBis])=7))));
Wenn([Eintritt]>[DatumVon] Und [Eintritt]<[DatumBis];DatDiff("t";[Eintritt];[DatumBis])-DatDiff("ww";[Eintritt];[DatumBis])*2+1+(Wochentag([Eintritt])=1)+(Wochentag([DatumBis])=7);
Wenn([Austrittd]>[DatumVon] Und [Austrittd]<[DatumBis];
DatDiff("t";[DatumVon];[Austrittd])-DatDiff("ww";[DatumVon];[Austrittd])*2+1+(Wochentag([DatumVon])=1)+(Wochentag([Austrittd])=7);DatDiff("t";[DatumVon];[DatumBis])-DatDiff("ww";[DatumVon];[DatumBis])*2+1+(Wochentag([DatumVon])=1)+(Wochentag([DatumBis])=7)))
Wie kann man das kürzen, bzw. gibt es einen besseren Weg ???
LG
Hallo
ZitatZum besseren Verständnis: Es gibt 6 Fälle:
1. Eintritt<Anfang<Ende<Austritt
2. Eintritt<Anfang<Austritt<Ende
3. Eintritt<Austritt<Anfang<Ende
4. Anfang<Eintritt<Ende<Austritt
5. Anfang<Eintritt<Austritt<Ende
6. Anfang<Ende<Eintritt<Austritt
Ich glaube nicht das dass jemand hier Versteht, was du mit dem Beispiel uns sagen möchstet. ???
Ich trette einem Verein bei am Tag (X) und scheide aus am Tag (y), diese Zeit kann Tag genau berechnet werden.
Hallo,
ZitatWie kann man das kürzen, bzw. gibt es einen besseren Weg
Ja, eine Kalendertabelle die alle Tage eines Zeitraums umfasst. Der Zeitraum kann problemlos 20 Jahre umfassen. In der Tabelle führt man noch 1 Häkchenfeld, mit dem man die Feiertage anhakt.
Dann wird die Ermittlung der Arbeitstage zu einem Einzeiler.
Hey
Okay, wollte damit nur klären, welche Funktionen abgedeckt sein sollen. ;) Das einzige Problem, das ich mit meiner Funktion habe tritt auf, wenn der Startpunkt der Abfrage Vor dem Eintritt in den Vertrag liegt und das Enddatum der Abfrage nach dem Austritt liegt.
In diesem Fall geht er schon nach der 1. Wenn-Funktion aus der Formel mit der Berechnung der Tage, die vom Eintritt bis zum Enddatum liegen.
Beispiel:
1. Eintritt: 01.08.2013; Austritt: 01.09.2013; Startdatum:01.08.2013; Enddatum: 01.10.2013; Tage: 24
2. Eintritt: 01.08.2013; Austritt: 01.09.2013; Startdatum:01.07.2013; Enddatum: 01.9.2013; Tage: 25
3. Eintritt: 01.08.2013; Austritt: 01.09.2013; Startdatum:01.08.2012; Enddatum: 01.10.2014; Tage: 300
Der 3. Fall sollte das Problem eigentlich verständlich machen. Je kleiner ich das Startdatum wähle, desto mehr Tage zählt er, obwohl der Eintritt erst viel später ist. Er springt also schon an der Stelle Wenn([Eintritt]>[DatumVon] Und [Eintritt]<[DatumBis];
DatDiff("t";[Eintritt];[DatumBis])-DatDiff("ww";[Eintritt];[DatumBis])*2+1+(Wochentag([Eintritt])=1)+(Wochentag([DatumBis])=7); aus dem Code. Mit meinem zweiten Code sollte es funktionieren, aber der ist angeblich zu lang! Habe es schon mit Kürzeln probiert=> selbes Ergebnis...
edit: Danke MzKlMu, ich setze mich morgen früh gleich davor!
Warum nimmt er die SQL-Anweisung denn nicht an? Sind es zu viele zu prüfende Argumente? Wie viele sind in einem Ausdruck erlaubt?
Ich habe mir das Beispiel http://www.office-loesung.de/ftopic442492_0_0_asc.php (http://www.office-loesung.de/ftopic442492_0_0_asc.php) mal angesehen (@MzKlMu ich denke, dass das von dir ist? :D)
Nur würde das meinen SQL-String nicht verkürzen, da die Kriterien für Eintritt in den und Austritt aus dem Vertrag immer noch so angegeben werden müssen, oder irre ich mich da?
Es gibt zwar Beispiele, aber ich finde keines für die Problemstellung.
Ist meine SQL-Anweisung nicht komprimierbar?
LG
Hallo,
ich würde in diesem Fall diese "Monster-Wenn-Geschichte" in eine Public-Funktion auslagen (dort kann man das Ganze wesentlicht übersichtlicher und ausführlicher behandeln) und in der Abfrage lediglich die Funktion aufrufen...
Alles klar. Wenn ich die Lösung habe, poste ich sie.
Danke
Hallo,
ich habe die Bedingungen noch nicht ganz begriffen. Könntest Du das bitte noch mal etwas genauer erklären?
Vor allen Dingen, was es mit dem Start und Endedatum auf sich hat.
Ich habe eine Abfrage über einen variablen Zeitraum ( von wann(Anfang) bis wann (Ende) wird abgefragt).
Daraufhin wird die Anzahl der Arbeitstage (Werktage) für jeden Mitarbeiter berechnet. Aber nur für die Zeit, in der er in den Vertrag eingetreten war. Die Tage VOR dem Eintritt und die Tage NACH dem Austritt werden nicht mit berechnet.
Das ganze dient dazu Personalkosten zu errechnen.
Wenn man sich nun ein ganzes Jahr ansieht, kommt es vor, dass Mitarbeiter innerhalb des Jahres ein- und austreten. Um die Kosten realistisch zu errechnen, dürfen diese erst nach Eintritt mitgerechnet werden und bei einem Austritt nicht mehr mitgerechnet werden (ergibt die Anzahl der Arbeitstage).
Meine erste Funktion hat, wenn Eintritt und Austritt aus dem Vertrag im angesehenen Zeitraum lagen, die Tage vor dem Eintritt mitgerechnet.
Vielleicht macht das Bild das deutlich ;) (Rot erzeugt Fehler, Grün alles richtig)
[Anhang gelöscht durch Administrator]
Hallo,
das versteh ich jetzt auch nicht....
Die Arbeitstage brauchen doch nur mit dem Eintritts- und Austrittsdatum berechnet werden und haben mit dem Filter-Datums-Bereich nichts zu tun, außer der Tatsache, dass sinnvollerweise das akt. Datum als "Austrittsdatum" benutzt werden müßte, wenn ein Mitarbeiter akt. noch "aktiv" , also nicht "ausgetreten" ist...
Ich halte auch mehr von der Verwendung einer Kalendertabelle:
PARAMETERS
[DatumVon] DateTime,
[DatumBis] DateTime
;
SELECT
X.PersonalID,
Count(X.Kalendertag) AS AnzahlArbeitstage
FROM
(
SELECT
P.PersonalID,
K.Kalendertag
FROM
(
SELECT
Kalendertag
FROM
Kalendertabelle
WHERE
Wochentag < 6
AND
Feiertag = False
AND
Kalendertag Between [DatumVon] AND [DatumBis]
) AS K, PersonalTabelle P) AS X,
DeineTabelle AS D
WHERE
X.Kalendertag BETWEEN D.Eintritt
AND
IIf(D.Austritt Is Null, Date(), D.Austritt)
AND
X.PersonalID = D.PersonalID
GROUP BY
X.PersonalID
;
Einige Details bezüglich einer Kalendertabelle kann man auch hier nachlesen: Grundlagen - SQL ist leicht (3) - Kalendertabelle (http://www.ms-office-forum.net/forum/showthread.php?t=298670)
MfGA
ebs
Tausend Dank Leute!!! ;D ;D
Ich habe mich für den Tipp von DF6GL entschieden, da ich ihn schneller umsetzen kann und das Ergebnis auch richtig ist ;)
DateDiff("d",[Eintrittd],[Austrittd])-DateDiff("ww",[Eintrittd],[Austrittd])*2+1+(Weekday([Eintrittd])=1)+(Weekday([Austrittd])=7) AS Arbeitstage
IIf([Eintritt]<[DatumVon],[DatumVon],[Eintritt]) AS Eintrittd, IIf(IsNull([Austritt]),[DatumBis],IIf([Austritt]>[DatumBis],[DatumBis],[Austritt])) AS Austrittd
WHERE (((IIf([Eintritt]<[DatumVon],[DatumVon],[Eintritt])) Between [DatumVon] And [DatumBis]) AND ((IIf(IsNull([Austritt]),[DatumBis],IIf([Austritt]>[DatumBis],[DatumBis],[Austritt]))) Between [DatumVon] And [DatumBis]))
Würde gerne noch ein paar mal Danke klicken :D
(Zusätzliche Lösung in Entwurfsansicht auf Anfrage)
LG
Hallo habe das selbe Problem, möchte aber keine Tage sondern Monate als Ergebnis haben.
Was muss ich im Feld Steuerelementinhalt für einen Code eingeben.
Gibt es hierfür eine Lösung?
Besten Dank für eure Mitteilung.
Hallo,
"möchte aber keine Tage sondern Monate als Ergebnis"
dann musst du eben die DateDiff-Funktion entsprechend anpassen - OH!
Leider kenne ich mich mit den Befehlen überhaupt nicht aus.
Die Eintritte haben immer den 1. Tag des Monats im Feld. z.B. 01.01.2014 oder 01.03.2014
Die Austritte haben immer den letzten Tag des Monats im Feld, z.B. 31.03.2014 oder 30.04.2014
Gibt's keine einfache Lösung.
Besten Dank für die Hilfe.
Hallo,
als Steuerelementinhalt eines Formular/Berichtsfeldes:
=DatDiff("m";[Eintritt];[Austritt])+1
Oder alternativ, als berechnetes Feld einer Abfrage:
Dauer: DatDiff("m";[Eintritt];[Austritt])+1
Ich würde die Abfrage bevorzugen und das Formularfeld an das berechnete Feld (Dauer) binden.
Hallo zusammen
Habe leider erst heute wieder Zeit gehabt mein Problem zu lösen.
Habe nun folgende Abfrage eingegeben:
SELECT DISTINCT SA1.Liegenschaftenname, SA1.Objekt, SA1.Mietbeginn, [vondat] AS Ausdr1, [bisdat] AS Ausdr2, SA1.Ende2,
DateDiff("m",[Mietbeginnd],[Ende2d])+1 AS Monate,
IIF([Mietbeginn]<[Ausdr1],[Ausdr1],[Mietbeginn]) AS Mietbeginnd, IIF(IsNull([Ende2]),[Ausdr2],IIF([Ende2]>[Ausdr2],[Ausdr2],[Ende2])) AS Ende2d,
WHERE
(((IIF([Mietbeginn]<[Ausdr1],[Ausdr1],[Mietbeginn])) Between [Ausdr1] And [Ausdr2] AND ((IIf(IsNull([Ende2]),[Ausdr2],IIf([Ende2]>[Ausdr2],[Ausdr2],[Ende2]))) Between [Ausdr1] And [Ausdr2])),
ORDER BY SA1.Liegenschaftenname, SA1.Objekt;
Leider zeigt er mir beim Ausführen einen Syntaxfehler an: Fehlender Operator in Abfrageausdruck Where
Weiss jemand Rat?
Danke für eure Hilfe
Hi,
entferne die Kommas vor WHERE und ORDER BY.
...außerdem fehlt der FROM-Teil. Und außerdem sind die Klammern im Where-Teil nicht paarig, am besten, du reduzierst sie auf das nötige Mindestmaß, dann fällt das Abzählen leichter.