Summe von Zeitdifferenzen

[tsunami]

Hallo zusammen,
ich bin derzeit krankgeschrieben und habe sowas wie eine Zeitübersicht erstellt.
Eine kleine DB mit zwei Tabellen:

stati
id|statiname|farbe

taetigkeiten
id|time|mitarbeiter_id|stati_id

Im Browser wird dann einfach dargestellt:
Tag 1 vom Mai 2024
Ein div Anfahrt 120 px = 90 Minuten -> rot
div Kunde 400 px = 4 h -> grün
div Anfahrt 120 px = 90 Minuten -> rot
div Bürp 20 px = 15 Minuten -> orange

Tag 2 Mai 2024
div Kunde grün 2 h
....

select t.*, stati.statiname, stati.farbe, timeSTAMPdiff(minute,(select time from taetigkeiten t1 where date_format(time,'%d')=1 and date_format(time,'%m')=5 AND t1.mitarbeiter_id = t.mitarbeiter_id and t1.id < t.id order by t1.id desc limit 1), time) diff from taetigkeiten t,stati where date_format(time,'%d')=1 and date_format(time,'%m')=5 and mitarbeiter_id=1 and stati_id=stati.id;

Damit berechne ich den Unterschied zu nächsten Event. Nun möchte ich etwas Statistik machen. Also Gesamtzeit beim Kunden, Gesamtfahrzeit im Monat des Mitarbeiters, Durchschnittliche Fahrteiten aller Mitarbeiter usw.

Ich dachte erst ein simples sum oder group by löst das Ganze. Ist aber ein Satz mit x, da er mir dann entweder die gesamte Summe rauswirft oder aber die Unterschiede zB vom 01.05. und vom 2.05. mitberechnet. Bedeutet also, wenn dei Rückfahrt am 01.05.24 um 16:30 beginnt um 17 Uhr zu Ende ist, am nächsten Morgen startet er um 08:00 - 08:15 Anfahrt habe ich eine Summe von 15 h anstatt 45 Minuten

Rauskommen soll halt sowas wie:
Kunde: 135 h
Fahrzeit 6,5 h
Büro 4 h
sonstiges 3 h

Dazu müsste ich die Zeitdifferenzen Tageweise aufsummieren und per stati gruppieren. Aber das bekomme ich nicht hin. Hatte schon an ein Array gedachrt. Aber dann müsste ich etliche Arrays haben und die Flexibilität geht verloren.

Testweise habe ich bei den Dummydaten jeden Tag den Status 99 = Feierabend eingefügt. Komme damit aber auch nicht weiter. Window-Funktionen habe ich nicht zur Verfügung (LAG).

Any Idea?
mfG
tsunami

 

[dabadepdu]

Du müsstest vielleicht das Modell ändern.
Entweder Du nimmst Anfang und Ende in einen Datensatz, das dann am besten als TimeRange Typ, falls mySQL das kann.
Oder Du musst mit Verweisen oder Tags arbeiten, die eine Zuordnung zusammengehöriger Events erlauben, wenn sie wie derzeit als reine Liste von Einzelereignissen geführt werden.
Also einfach gesagt eine Flag Spalte, die Anfang und Ende kennzeichnet, damit man es zusammenbringen kann.
Das wäre aber auch mühsam.
Besser wäre vielleicht ein Verweis, der vom Ende Datensatz auf den Beginndatensatz zeigt (ID). Das würde auch einige Join Logik in der Abfrage sparen.

Wie man es am besten macht, hängt von den Haupteinsatzfällen ab. Vielleicht gibt es viele Einträge, die nur ein Zeitpunkt sind. Dann reine Eventliste, manche mit Rückverweis, falls sie Anfang und Ende haben.
Oder es sind immer Angaben von Zeiträumen, dann alles direkt in einen Datensatz, Anfang und Ende bzw. Timerange.

Wenn Du das Modell nicht ändern willst oder kannst, musst Du einen Weg finden, aus den gegebenen Daten zu bestimmen, welche Datensätze genau Anfang und Ende einer Zeitspanne definieren. Das ist u.U. nicht trivial, wenn durch die Art der Nutzung nicht sichergestellt ist, dass zu 110% auch immer ein Datensatzpärchen mit diesen Angaben vorliegt. Z.B. wenn schlicht der Endeeintrag kurz vor Feierabend vergessen wurde.

 

[ukulele]

Ich verstehe dein Datenmodel nicht wirklich, woher kommen jetzt die 90 Minuten?

Also auf jeden Fall muss jede Zeitbuchung einen Datumsbezug und eine Dauer oder ein Start und Ende haben, sonst kannst du nichts berechnen. Das muss in einer normalisierten Form vorliegen. Natürlich kann ich aus einer Kette von Ereignissen auch Zeitbuchungen ableiten, dafür sind dann Window-Funktionen ganz besonders hilfreich (Warum stehen die nicht zur Verfügung?).

 

[dabadepdu]

Ich verstehe dein Datenmodel nicht wirklich, woher kommen jetzt die 90 Minuten?
..

Natürlich kann ich aus einer Kette von Ereignissen auch Zeitbuchungen ableiten, dafür sind dann Window-Funktionen ganz besonders hilfreich (Warum stehen die nicht zur Verfügung?).

Wenn das Datenmodell eine exakte Zuordnung je Tag, Kunde, MA, ... zulässt, könnte man auch mit Windows Functions arbeiten, zumindest bei aktuellen mySQL / maria Versionen?). Voraussetzung auch hier, dass alle Pärchen vollständig sind, sonst ist es doof.

 

[tsunami]

Hallo zusammen,
ich habe mal eine Skizze angehängt um das Ganze zu verdeutlichen. Normalerweise ist doch das Ende von Status 1, automatisch der Start von Status 2. Daher dachte ich mir das spare ich mir. Kritsch ist nur der Start und das Ende. Also was weiß ich, der Tag geht von 7:15 - 18:00.
Deswegen den Status 99 als Feierabend.. Ansonsten brauche ich doch nur die Differenz zum näcjhsten Status.
Ich habe aktuell nur ein Android Tablet und da gibt es keine Windows Funktionen.

Es müsste sowas sein, wie summe Differenzen per status per Monat. Entweder fange ich die ab, bevor die Übersicht ausgegeben wird und summiere das Ganze auf oder ich kriege es in einem seperaten SQL Query hin. Eine Idee ist noch per $$ die Variable zu erzeugen und zu füllen. Via numbers habe ich die Anzahl der Tage des Monats. Dann for-Schleife die Differenzen berechnen und aufsummieren. Aber wenn zB an einem Tag reines HomeOffice war, ist Fahrt =0 am 2024-05.04 ...

 

[ukulele]

Also du hast eine Reihe von Zeitpunkten und die Zwischenräume sind deine Tätigkeiten. Die erste Buchung ist Status 1, Folgebuchungen zählen den Status hoch, die letzte ist immer Status 99. Kann man machen, daraus muss man sich dann die Zeiträume generieren, die kann man dann im zweiten Schritt aggregieren.

Window-Functions wären dabei hilfreich, du bist nicht weiter darauf eingegangen, warum du die nicht nutzen kannst.

Ansonsten müsste es mit Joins gehen, etwa so (MSSQL Beispiel):

WITH tabelle(datum,zeit,[status]) AS (
    SELECT cast('2020-01-01' AS DATE),cast('08:00' AS TIME),1 UNION ALL
    SELECT '2020-01-01','13:00',2 UNION ALL
    SELECT '2020-01-01','13:30',3 UNION ALL
    SELECT '2020-01-01','17:00',99
    )
SELECT    t1.datum,
        t1.zeit AS [start],
        isnull(t2.zeit,t3.zeit) AS [ende],
        t1.[status]
FROM    tabelle t1
LEFT JOIN tabelle t2
ON        t1.datum = t2.datum
AND        t1.[status] + 1 = t2.[status]
LEFT JOIN tabelle t3
ON        t1.datum = t3.datum
AND        t3.[status] = 99
WHERE    t1.[status] != 99

2020-01-01 08:00:00.0000000 13:00:00.0000000 1
2020-01-01 13:00:00.0000000 13:30:00.0000000 2
2020-01-01 13:30:00.0000000 17:00:00.0000000 3

Das ist erstmal ein schlichtes Beispiel. Es muss sichergestellt sein, das der Status nicht doppelt am selben Tag vor kommt und nicht über 97 hinaus geht. Aus dem Ergebnis kann man dann die Zeitdifferenz berechnen, diese filtern/gruppieren und aggregieren. Also mal ein bisschen weiter gesponnen:

WITH tabelle(datum,zeit,[status],typ) AS (
    SELECT cast('2020-01-01' AS DATE),cast('07:30' AS TIME),1,'Anfahrt' UNION ALL
    SELECT '2020-01-01','08:00',2,'Arbeit' UNION ALL
    SELECT '2020-01-01','13:00',3,'Pause' UNION ALL
    SELECT '2020-01-01','13:30',4,'Arbeit' UNION ALL
    SELECT '2020-01-01','17:00',5,'Rückweg' UNION ALL
    SELECT '2020-01-01','17:30',99,NULL
    )
SELECT    t1.datum,
        sum(datediff(minute,t1.zeit,isnull(t2.zeit,t3.zeit))) AS sum_dauer,
        (CASE WHEN t1.typ IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.typ END) AS typ
FROM    tabelle t1
LEFT JOIN tabelle t2
ON        t1.datum = t2.datum
AND        t1.[status] + 1 = t2.[status]
LEFT JOIN tabelle t3
ON        t1.datum = t3.datum
AND        t3.[status] = 99
WHERE    t1.[status] != 99
GROUP BY t1.datum,(CASE WHEN t1.typ IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.typ END)

2020-01-01 510 Arbeit
2020-01-01 60 Fahrtzeit
2020-01-01 30 Pause

Man kann natürlich auch nur nach typ gruppieren, ich habe mal als Erweiterung die Typen Anfahrt und Rückweg zu Fahrzeit zusammen gefasst, um zu zeigen, was möglich und sinnvoll sein kann.

 

[tsunami]

Es kann leider passieren, dass man zweimal ins Büro fährt oder von Kunde A nach Kunde b

 

[ukulele]

Es kann leider passieren, dass man zweimal ins Büro fährt oder von Kunde A nach Kunde b

Und was wäre das Problem in so einem Fall? Der Code bleibt gleich.

WITH tabelle(datum,zeit,[status],typ) AS (
    SELECT cast('2020-01-01' AS DATE),cast('07:30' AS TIME),1,'Anfahrt' UNION ALL
    SELECT '2020-01-01','08:00',2,'Arbeit' UNION ALL
    SELECT '2020-01-01','13:00',3,'Pause' UNION ALL
    SELECT '2020-01-01','13:30',4,'Arbeit' UNION ALL
    SELECT '2020-01-01','17:00',5,'Rückweg' UNION ALL
    SELECT '2020-01-01','17:30',99,NULL UNION ALL
    SELECT '2020-01-02','07:30',1,'Anfahrt' UNION ALL
    SELECT '2020-01-02','08:00',2,'Arbeit' UNION ALL
    SELECT '2020-01-02','10:30',3,'Anfahrt' UNION ALL
    SELECT '2020-01-02','10:45',4,'Extern' UNION ALL
    SELECT '2020-01-02','12:00',5,'Rückweg' UNION ALL
    SELECT '2020-01-02','12:15',6,'Arbeit' UNION ALL
    SELECT '2020-01-02','13:00',7,'Pause' UNION ALL
    SELECT '2020-01-02','13:30',8,'Arbeit' UNION ALL
    SELECT '2020-01-02','17:00',9,'Rückweg' UNION ALL
    SELECT '2020-01-02','17:30',99,NULL
    )
SELECT    t1.datum,
        sum(datediff(minute,t1.zeit,isnull(t2.zeit,t3.zeit))) AS sum_dauer,
        (CASE WHEN t1.typ IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.typ END)
FROM    tabelle t1
LEFT JOIN tabelle t2
ON        t1.datum = t2.datum
AND        t1.[status] + 1 = t2.[status]
LEFT JOIN tabelle t3
ON        t1.datum = t3.datum
AND        t3.[status] = 99
WHERE    t1.[status] != 99
GROUP BY t1.datum,(CASE WHEN t1.typ IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.typ END)
ORDER BY t1.datum

2020-01-01 510 Arbeit
2020-01-01 60 Fahrtzeit
2020-01-01 30 Pause
2020-01-02 405 Arbeit
2020-01-02 75 Extern
2020-01-02 90 Fahrtzeit
2020-01-02 30 Pause

 

[tsunami]

@ukulele Erstmal vielen Dank. Zwei Fragen:
Was bedeutet der Part

WITH tabelle(datum,zeit,[status],typ) AS (<br> SELECT cast('2020-01-01' AS DATE),cast('07:30' AS TIME),1,'Anfahrt' UNION ALL<br> SELECT '2020-01-01','08:00',2,'Arbeit' UNION ALL ....

Gehört das zur Abfrage?

Und

SELECT '2020-01-01','08:00',2,'Arbeit' UNION ALL

SELECT '2020-01-01','13:00',3,'Pause' UNION ALL<br>

SELECT '2020-01-01','13:30',4,'Arbeit' UNION ALL<br> SELECT '2020-01-01','17:00',5,'Rückweg' UNION ALL

...

Da habe ich mich glaube ich nicht korrekt ausgedrückt: Ich möchte sowas haben wie:
Auswahl Mai 2024 - Mitarbeiter 1
Büro 300
Kunde 1245
Fahrt 246
Also nicht pro Tag, sondern die Summe pro Status des Monates . Das ich sehen kann er war 1246 Minuten beim Kunden, 300 Minuten im Büro und saß 246 Minuten im Auto. Dabei spielt es keine Rolle, ob er bei Kunde A, B oder C war. Also nur wie kommen die monatlichen Zeiten zustande.

Aber ich habe einen groben php-Ansatz im Kopf:
sql .... wie gehabt die einzelnen Stati, mysqli_query...
While($ausgabe=mysqli_fetch_assoc($msquery))
{
$$statiname=$$statiname+$ausgabe['statiname'];
echo "<div...>".$ausgabe['statiname']."</div>";
}
ALso das aus dem Status eine Variable mit dem Statusnamen generiert wird und die Werte aufsummiert werden...

 

[ukulele]

In meinem Beispiel-Fall gehört das zum SQL Statement. WITH tabelle AS () legt dabei eine Tabelle mit Testdaten zur Laufzeit der Abfrage an. Die Tabelle besteht nur aus statischen Werten die ich mit UNION ALL verkette, alles nur, um beispielhafte Test-Daten zu erhalten.

Du kannst den Code komplett weglassen und mit SELECT beginnen, allerdings sucht er dann natürlich eine Tabelle namens "tabelle" und die entsprechenden Spalten. Das müsstest du gegen deine eigenen Daten austauschen, theoretisch wäre das dann aber auch mit deinen Daten lauffähig (wenn ich das Format getroffen habe).

Da ich auf MSSQL teste und du MySQL verwendest, müsstest du auch noch die Funktionen datediff() und isnull() ersetzen, die beiden SQL Dialekte unterscheiden sich vor allem bei Funktionen. Es kann auch sein das der WITH Teil bei dir nicht mit ausgeführt werden kann, abhängig vom Alter deiner MySQL Version.

 

[tsunami]

SELECT t1.time,
sum(timeSTAMPdiff(minute,t1.time,t2.time)) AS sum_dauer,
(CASE WHEN t1.stati_id IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.stati_id END)
FROM taetigkeiten t1
LEFT JOIN taetigkeiten t2
ON t1.time = t2.time
AND t1.status + 1 = t2.status
LEFT JOIN taetigkeiten t3
ON t1.time = t3.time
AND t3.stati_id = 99
WHERE t1.stati_id != 99
GROUP BY t1.time,(CASE WHEN t1.typ IN ( 'Anfahrt','Rückweg' ) THEN 'Fahrtzeit' ELSE t1.typ END)
ORDER BY t1.time

Leider kommt da überall Null raus und vor allem 20 Datensätze statt 5 oder 6

 

[ukulele]

In Zeile 7 beziehst du dich noch auf "status", wie ich in meinem Test. In Zeile 10 auf "stati_id", das wäre vermutlich in Zeile 7 auch richtig. Wenn er aber keinen Fehler wirft dann scheint es zusätzlich eine Spalte "status" zu geben...

Ist wirklich alles NULL, also auch die Spalte t1.time?

 

[tsunami]

Die summe.

 

[ukulele]

Das ergibt alles keinen Sinn, was steht denn in stati_id? Danach willst du doch gar nicht gruppieren, auch nicht in dem CASE. Kannst du mal das Ergebnis von
SELECT * FROM taetigkeiten
für den Tag 1 vom Mai 2024
und
SELECT * FROM stati
posten? Als Text, nicht als PDF und mit Spaltennamen bitte.

 

[tsunami]

Stati_id ist doch die Verknüpfung zur Tabelle stati, d.h. die Summe soll pro Status, also pro stati_id errechnet werden...

id time stati_id mitarbeiter_id
1 2024-05-01 07:30:00 1 1
2 2024-05-01 08:00:00 2 1
3 2024-05-01 09:45:00 3 1
11 2024-05-01 07:00:00 1 2
12 2024-05-01 07:45:00 3 2
13 2024-05-01 08:00:00 4 2
14 2024-05-01 09:00:00 3 2
15 2024-05-01 17:00:00 2 2
28 2024-05-01 17:00:00 99 1
29 2024-05-01 17:00:00 99 2
30 2024-05-01 17:00:00 99 3

1 Fahrt red
2 Kunde green
3 Büro brown
4 Pause blue
5 sonstige grey
99 feierabend black