Summe von Zeitdifferenzen

[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.

Diese Annahme von mir ist in deinen Beispieldaten nicht gegeben. taetigkeiten.id zählt zwar hoch, fängt aber nicht beim selben Wert an und für taetigkeiten.status_id = 99 findet auch in taetigkeiten.id ein unkalkulierbarer "Sprung" statt. taetigkeiten.status_id zählt auch nicht hoch, das ist nur der Fremdschlüssel auf status und wechselt dementsprechend.

In der Folge gibt es keinen Wert der zusammenhängend eine Reihenfolge bildet. Und damit kannst du meinen ersten Code total vergessen - kann nicht funktionieren. Das muss auch nicht, dafür gibt es ja Window-Functions, mit denen man Daten in Reihenfolge bringen kann.

Window-Funktionen habe ich nicht zur Verfügung (LAG).

Ich frage nochmal, wieso nicht? MySQL unterstützt das schon seit einigen Versionen.

Ohne Window-Functions wird das echt nicht schön. Man müsste es mit einem Subselect lösen, der kostet Performance. Hier mal ein Beispiel:

SELECT    tt.mitarbeiter_id,
        convert(DATE,tt.[time]) AS datum,
        tt.statiname,
        sum(datediff(minute,tt.[time],tt.time_bis)) AS sum_dauer
FROM    (

SELECT    taetigkeiten.*,
        stati.statiname,
        stati.farbe,
        (    SELECT    TOP 1 t.[time]
            FROM    taetigkeiten t
            WHERE    t.mitarbeiter_id = taetigkeiten.mitarbeiter_id
            AND    (    t.[time] > taetigkeiten.[time]
            OR        t.[time] = taetigkeiten.[time]
            AND        t.stati_id = 99 )
            ORDER BY t.[time] ASC
            --LIMIT 1
            ) AS time_bis
FROM    taetigkeiten
INNER JOIN stati
ON        taetigkeiten.stati_id = stati.id
WHERE    taetigkeiten.stati_id != 99

    ) tt
GROUP BY tt.mitarbeiter_id,convert(DATE,tt.[time]),tt.statiname
ORDER BY tt.mitarbeiter_id,convert(DATE,tt.[time]),tt.statiname

(Das ist MSSQL. Für MySQL wirst du das TOP 1 streichen müssen und durch das LIMIT 1 ersetzen, das ich hier auskommentiert habe. Auch datediff() und convert(DATE,<datetime_spalte>) müsstest du ersetzen.)

mitarbeiter_id datum statiname sum_dauer
-------------- ---------- ---------- -----------
1 2024-01-05 Büro 435
1 2024-01-05 Fahrt 30
1 2024-01-05 Kunde 105
2 2024-01-05 Büro 495
2 2024-01-05 Fahrt 45
2 2024-01-05 Kunde 0
2 2024-01-05 Pause 60

In deinen Daten gibt es auch noch eine Ungereimtheit. Mitarbeiter 2 hat um 17 Uhr Büro gestempelt und feierabend ist auch 17 Uhr. Ist das richtig? - Ich habe den Fall berücksichtigt, das ist die Position Kunde = 0 Minuten.

 

[tsunami]

Ich habe mysql auf Android. Ksweb. Das einzige, was ich gefunden habe und einigermaßen tuts. Da gibt es keine Windows Funktionen.
Ok, bei den dummydaten ist noch ein bock drin. Allerdings kann es doch auch vorkommen, dass Kunde nicht da ist, trotz Termin. Dann hätte ich Anfahrt zb 15 Minuten, Kunde 0.
Den Code checke ich nacher. Bin fast soweit, dass Ganze in php versuchen zu lösen. Stati als key und diff als value. Oä.

 

[ukulele]

Ich habe mysql auf Android. Ksweb. [...] Da gibt es keine Windows Funktionen.

Okay das ist möglicherweise eingeschränkt, wobei ich auf die Schnelle nichts dazu finde. "Window-Functions" haben aber auch nichts mit "Windows" zu tun, eventuell ein Missverständnis? Du kannst leicht testen, ob das geht:

SELECT *,ROW_NUMBER() OVER (PARTITION BY mitarbeiter_id ORDER BY time) AS test FROM taetigkeiten

Die Spalte "test" nutzt eine gänige Window-Function. Wenn dein MySQL das unterstützt, müsste er die Zeilen durchnummerieren.

Ok, bei den dummydaten ist noch ein bock drin. Allerdings kann es doch auch vorkommen, dass Kunde nicht da ist, trotz Termin. Dann hätte ich Anfahrt zb 15 Minuten, Kunde 0.

Ja das habe ich mit eingebaut, Besonderheiten gibt es immer mal. Insofern ja gut wenn das jetzt gemacht wird, sonst wunderst du dich irgendwann warum die Daten nicht passen. Ich kenne ja nur ein Beispiel und orientiere mich daran.

Den Code checke ich nacher. Bin fast soweit, dass Ganze in php versuchen zu lösen. Stati als key und diff als value. Oä.

Alles in allem ist das immer noch recht einfach wenn man SQL kennt. PHP kann das vermutlich auch aber ist halt wie mit einem Betonmischer zum Bäcker fahren - wenn nichts anderes da ist...

 

[tsunami]

Unter Windows geht es. Unter Android nicht. Hatte es mit Lag versucht
=> unknown function lag

 

[tsunami]

Der Code gjbt mir die Summen pro Tag.

 

[ukulele]

Der Code gjbt mir die Summen pro Tag.

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

Damit hast du die Grundlage für all das. Wenn du jetzt keine Statistik pro Tag haben willst sondern für einen Monat, dann gruppierst du nicht nach Tag sondern nach Monat.

 

[tsunami]

Hallo,

ich habe es nun via php gelöst. Für Interessierte:


<?php

require_once ('konfik.php');
$db_link = mysqli_connect ( MYSQL_HOST, MYSQL_BENUTZER, MYSQL_KENNWORT, MYSQL_DATENBANK );

if(isset($_POST['selmonth']))
$selmonth=$_POST['selmonth'];
else
$selmonth=5;
if(isset($_POST['mitarbeiter_id']))
$mitarbeiter_id=$_POST['mitarbeiter_id'];
else
$mitarbeiter_id=1;

if(isset($_POST['selyear']))
$selyear=$_POST['selyear'];
else
$selyear=2024;
$array=array();
for($z=1; $z<=6;$z++){
$sql_tagesdiv = sprintf( "SELECT t.*, stati.statiname, stati.farbe,stati.id as statiid, TIMESTAMPDIFF(MINUTE,time, (SELECT time FROM taetigkeiten t1 WHERE DATE_FORMAT(time,'%%d') = %s AND DATE_FORMAT(time,'%%m') = %s AND DATE_FORMAT(time,'%%Y') = %s AND t1.mitarbeiter_id = t.mitarbeiter_id AND t1.id > t.id ORDER BY t1.id asc LIMIT 1)) AS diff FROM taetigkeiten t, stati WHERE DATE_FORMAT(time,'%%d') = %s AND DATE_FORMAT(time,'%%m') = %s AND DATE_FORMAT(time,'%%Y') = %s AND mitarbeiter_id = %s AND stati_id = stati.id",
mysqli_real_escape_string($db_link, $z),
mysqli_real_escape_string($db_link, $selmonth),
mysqli_real_escape_string($db_link, $selyear),
mysqli_real_escape_string($db_link, $z),
mysqli_real_escape_string($db_link, $selmonth),
mysqli_real_escape_string($db_link, $selyear),
mysqli_real_escape_string($db_link, $mitarbeiter_id) );
$mysqlresult_tag = mysqli_query($db_link, $sql_tagesdiv);
echo "<br><br>".$sql_tagesdiv;
while($ausgabe=mysqli_fetch_assoc($mysqlresult_tag))
{
echo $ausgabe['statiname']." - ".$ausgabe['diff']."<br>";
if(!array_key_exists($ausgabe['statiname'],$array))
$array[$ausgabe['statiname']] = $ausgabe['diff'];
else
{
$summe_arraywert=$array[$ausgabe['statiname']]+$ausgabe['diff'];
$array[$ausgabe['statiname']]=$summe_arraywert;
}
}
}
foreach($array as $key=>$value)
{
echo $key."->".$value."<br><br>";
}

?>

Das macht genau das was ich will. Vielen Dank für die Mühe
tsunami