Office: Zeiträume addieren mit Zeitüberlappung

Ich beziehe mich auf ein Posting vom 23.08.09 (Frage vom User tcg9000) und habe das selbe Problem. Es geht darum, zu einer gegebenen ID die gesamte Differenzzeit zwischen Stopp und Start zu ermitteln, wobei überlappende Zeiten nicht in die Berechnung einfließen dürfen.

Leider tritt in der angegebenen Lösung noch ein Fehler auf, wenn die Daten z. B. in der folgenden Reihenfolge angeordnet sind.


ID ... Start ... Stopp
--------------------------
1 ... 0:20 ... 0:50
1 ... 0:30 ... 0:45
1 ... 0:35 ... 0:40


Die Ausgabe ergibt:

ID ... Start ... Stopp
--------------------------
1 ... 0:20 ... 0:50 = 30 Minuten
1 ... 0:30 ... 0:45 = 15 Minuten


Richtig wäre aber:

ID ... Start ... Stopp
--------------------------
1 ... 0:20 ... 0:50 = 30 Minuten


Hier nochmals der Code vom damaligen Posting:

Code:

Im Anhang befindet sich ein Muster der Datenbank und SQL-Abfrage.

Da ich kein SQL-Guru bin, habe ich es nach vielen Versuchen nicht geschafft, eine Lösung zu finden.

Vielleicht kann mir jemand helfen?

:)

 

Hallo,

so sollte es klappen:

select id, min(start), max(stop) , datediff("n",min(start), max(stop))
from (
SELECT t1.ID, Min(t1.start) AS Start, "" AS Stop
FROM t1 where id = 1
GROUP BY t1.ID
union
SELECT t1.ID, "", Max(t1.stop) AS Stop
FROM t1 where id = 1
GROUP BY t1.ID
) group by id

 

probiere einmal das aus:
Code:

@Bernd: bei deiner Variante wird es ein Problem geben, wenn die Zeiten nicht durchgängig sind.

 

\@Josef P.
uups ! Danke für den Hinweis!

 

\@Josef P.
Deine Query hat das selbe Problem mit den nicht durchgängigen Zeiten:

Beispiel:
00:20 - 00:25
00:30 - 00:50
00:35 - 00:40
Liefert bei Deinem und meinem Sql 30 Minuten was falsch ist.

Da ich aber demnächst genaus so ein Problem lösen muss nämlich "wie lange wurde an einem Produktonsuaftrag gearbeitet von Start bis Stop unter Berücksichtigung von Pausen" und es können beliebig viele Aktivitäten parallel laufen, würde ich es berüßen, wenn der Thread diese Problem lösen könnte.

 

Meine SQL-Anweisung liefert für diesen Fall das richtige Ergebnis. Es ist aber trotzdem noch ein Fehler enthalten.

so etwas klappt nicht:
ID Start Stopp
1 00:20:00 00:31:00
1 00:30:00 00:45:00
1 00:35:00 00:50:00

/edit: für diese Szenario fällt mir keine reine SQL-Lösung ein. Man müsste das erste Ergebnis wieder mit der gleichen SQL-Anweisung überprüfen, bis es keine Überschneidungen gibt.

 

\@Josef P.
Wenn keine Pausen sind liefern Deine und meine das selbe.
Wenn Pausen sind nicht.

Lass mal Deine Query in der angehängten DB laufen sie liefert 2 Zeile, 5 und 20 Minuten.

 

Hi,

sollten Pausen dann nicht auch erkennbar sein (Statuskennzeichen o.ä.)? Oder gilt alles als Pause, was auf dem Zeitstrahl "unbenutzt" ist?

 

Ja. Was ist daran falsch?

Autw start stop ID
1 01.02.2010 12:20:00 01.02.2010 12:25:00 1
2 01.02.2010 12:30:00 01.02.2010 12:50:00 1
3 01.02.2010 12:35:00 01.02.2010 12:40:00 1

1: 5
2: 20
3: 0, da in 2 enthalten


Wenn es dir um die Arbeitszeit (nicht effektive Durchlaufzeit) geht, dann musst du nur summieren.

 

Hallo,

also meine Aufgabenstellung sieh so aus:

Es sind Aktivitäten registriert:

Reinigen 08:00 bis 09:30
Rüsten 10:00 bis 13:00
Wartung 10:00 bis 12:30 (parallel zum Rüsten)
Abnahme 13:15 bis 14:00
Produktion 14:00 bis 15:00
Techn. Störung 15:00 bis 15:15
Reparatur1 15:15 bis 16:00
Reparatur2 15:30 bis 16:00
Produktion 16:15 bis 22:30
Ausfahren 22:45 bis 23:30

Es soll errechne werden
1. wie lange die Anlage tatsächlich belegt war
2. die Differenz zwischen erstem "von" und spätesten "bis" und der Zeit in der Aktivitäten registriert wurden, also die Zeit die quasi vertrödelt wurde.

 

Einen Lösungsansatz (für beide Varianten) hätte ich noch - aber wieder einmal nur mit Hilfstabelle.

Hilfstabelle enthält je Minute eines Tages einen Datensatz.

Nun werden aus dieser Hilfstabelle jene Datensätze herausgefiltert, die in der Zeitraum-Tabelle vorkommen.
Die Summe ergibt dann die effektive Durchlaufzeit (oder Belegzeit).

Prinzip:
Code:

Anm.: Ich würde es aber mit der zuvor erwähnten Schleife probieren. Das wird vermutlich schneller ablaufen.