Gruppieren über Datumsgrenzen und mehrere Arbeitsschichten

[borukthedwarf]

Hallo

Ich habe folgende Frage.
Zur Erstellung der Kommissionierleistung in einem Lager will ich für jeden Mitarbeitenden pro Tag seine Anzahl Picks (Picks done) und die Menge (Amount done).

Mein Probleme sind im Moment
- dass es fast ein 24 Stunden Betrieb ist und ich nun Mühe habe mit dem Übergang bei Mitternacht
- Die Schichten können sich leicht überlappen, besonders der Übergang von Nacht zu Frühschicht am Morgen
- an mehreren Standorten sind die Schichten nicht immer gleich
- Es kommt oftmals vor, dass zwar die Schichten definiert sind von der Zeit, aufgrund der Arbeit die Personen jedoch früher/später gehen

Einschränkungen:
- Es gibt in keiner Tabelle ein Merkmal ob Mitarbeiter XY in der Nacht arbeitet/am Tag etc.
- Es können keine neuen Tabellen gemacht werden

Ziel:
- Früh- und Tagschicht sind ja relativ einfach zuzuordnen, diese sollen einfach dem aktuellen Tag sein
- Die Nachtschicht beginnt am Tag 1 und endet ihre Schicht am Tag 2, hier soll die ganze Leistung dem Tag 1 angerechnet werden

Hier wie die Schichten ungefähr aussehen

Ich hab es wie folgt versucht. Wenn die Zeit kleiner als 07:00 ist, soll alles dem VOrtag angerechnet werden, ansosten dem aktuellen Tag.
Leider ist das Ergebniss aber nicht korrekt.

SELECT
    picking.userid,
    TRUNC(CAST(FROM_TZ(picking.starttime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich') - INTERVAL '1' DAY * CASE WHEN EXTRACT(HOUR FROM CAST(FROM_TZ(picking.starttime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich') >= 7 THEN 0 ELSE 1 END AS pickingdate,
    COUNT(picking.pickedamount_baseqty) AS "Picks done",
    SUM(picking.pickedamount_baseqty) AS "Amount done"
FROM picking
LEFT JOIN pickingorders ON pickingorders.orderid = picking.orderid
WHERE CAST(FROM_TZ(picking.starttime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich' >= TIMESTAMP '2023-07-03 00:00:00'
AND CAST(FROM_TZ(picking.finishtime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich' <= TIMESTAMP '2023-07-22 23:59:59'
AND picking.usecase = 'PICKING'
GROUP BY
    picking.userid,
    TRUNC(CAST(FROM_TZ(picking.starttime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich') - INTERVAL '1' DAY * CASE WHEN EXTRACT(HOUR FROM CAST(FROM_TZ(picking.starttime, 'UTC') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'Europe/Zurich') >= 7 THEN 0 ELSE 1 END
ORDER BY
    picking.userid, pickingdate;

Hat hier jemand eine clevere Idee wie ich das kösen könnte?


Mfg

 

[Kampfgummibaerlie]

kann man bei deiner Datenbank mit Spaltentyp interval arbeiten? wenn ja, kleines Beispiel:

create table schichten(tag text, schichtzeit tsrange, person integer);
insert into schichten(tag, schichtzeit, person) values ('Tag 1', '(2023-08-28 21:00, 2023-08-29 08:00)', 1);
select upper(schichtzeit)-lower(schichtzeit) from schichten;

Result:

11:00:00

Hoffe ich hilft dir ein wenig

Erklärung zu interval:
das ist ein spaltentyp der immer die differenz zwischen zwei zeitpunkten beschreibt.
21:00 bis 24:00 sind 3:00:00
00:00 bis 08:00 sind 8:00:00

8 Stunden+3 Stunden = 11 Stunden

ich arbeite jedoch mit postgresql

EDIT:
wenn du mit einem 11 Stunden interval weiter "rechnen" möchtest, muss man die Sekunden ausrechnen, und dann evtl. auf Minuten umrechnen... (geht sicher auch spezifischer mit einem cast, aber ja... bin halt faul xD)
Beispiel:

select extract(epoch from (upper(schichtzeit)-lower(schichtzeit))) from schichten;

 

[borukthedwarf]

Mhm, müsste ich wohl einfach zuerst sortieren nach user und dann startzeit, damit ich die differenz Start zu Ende der Schicht habe (falls ich dich richtig verstehe).
Aber inwiefern würde mir das helfen?

Grundsätzlich sieht es etwa so aus, 1 Zeile ist praktisch 1 Pick

userid, starttime, finishtime, pickedamount_baseqty
User1, 17.07.2023 22:05:05, 17.07.2023 22:05:16, 3
User1, 17.07.2023 22:05:16, 17.07.2023 22:06:01, 6
....
User1, 17.07.2023 23:59:55, 18.07.2023 00:00:04, 1
User1, 18.07.2023 00:00:05, 18.07.2023 00:01:01, 6
User2, 18.07.2023 07:00:54, 18.07.2023 07:01:32, 5
User2, 18.07.2023 07:01:32, 18.07.2023 07:02:02, 3
....

Gruppiert:
userid, datum
User1, 17.07.2023, 16
User2, 18.07.2023, 8

 

[Kampfgummibaerlie]

Select user, min(datum), sum(zahl) group by user;

Sollte so gehen.

 

[ukulele]

Ich verstehe das so das alle picks gezählt werden sollen und dem Tag zugeordnet werden, in dem die Schicht beginnt. Wenn ein pick nach 0 Uhr statt findet aber vor 7 Uhr, dann handelt es sich automatisch um die Nachtschicht die am Vortag begonnen hat. starttime und finishtime beziehen sich auf den einzelnen pick, nicht auf die Schicht.

Hier mal pseudo-Code, ich hab mir jetzt mit Datumskonvertierungen keine Mühe gegeben das ist unter Oracle sowieso anders.

SELECT (CASE WHEN zeit_starttime < 7 THEN datum_starttime - 1_tag ELSE datum_starttime END) AS datum_pick,
count(1) AS count_picks,
sum(pickedamount_baseqty) AS sum_quantity
FROM tabelle (ggf. Join)
GROUP BY (CASE WHEN zeit_starttime < 7 THEN datum_starttime - 1_tag ELSE datum_starttime END)

- Ich habe mich jetzt nur auf starttime bezogen. Die picks scheinen ja immer recht kurz, die endtime kann eventuell auch über 7 Uhr hinaus reichen und noch zur Nachtschicht gehören.
- Dein Code hat ein paar generelle Schwächen: Die ganzen Zeitkonvertierungen nerven,
- der Left Join auf pickingorders ist in deinem Beispiel zumindest unnötig, theoretisch könnten hier Datensätze aus picking mehrfach gezählt werden, wenn es mehrere pickingorders pro picking geben kann.
- Auch verwendest du kein BETWEEN, das würde sich hier anbieten. Allerdings liebe ich Genauigkeit und bis <= 06:59:59 (das trifft ebenso BETWEEN) könnte z.B. bei einer höheren Genauigkeit im Zeitstempel Werte zwischen 06:59:59 und 07:00:00 nicht korrekt einschließen, daher dann doch bitte nur mit < 07:00:00 arbeiten.

Nur mal so nebenbei: Wenn du schon alle Zeitzonen auf Zürich konvertierst, geht denn eine Nachtschicht immer bis 7 Uhr morgens in Zürich? Warum zählst du pickings nicht nach Ortszeit zum Datum?

 

[borukthedwarf]

Ich verstehe das so das alle picks gezählt werden sollen und dem Tag zugeordnet werden, in dem die Schicht beginnt. Wenn ein pick nach 0 Uhr statt findet aber vor 7 Uhr, dann handelt es sich automatisch um die Nachtschicht die am Vortag begonnen hat. starttime und finishtime beziehen sich auf den einzelnen pick, nicht auf die Schicht.

genau

Hier mal pseudo-Code, ich hab mir jetzt mit Datumskonvertierungen keine Mühe gegeben das ist unter Oracle sowieso anders.

SELECT (CASE WHEN zeit_starttime < 7 THEN datum_starttime - 1_tag ELSE datum_starttime END) AS datum_pick,
count(1) AS count_picks,
sum(pickedamount_baseqty) AS sum_quantity
FROM tabelle (ggf. Join)
GROUP BY (CASE WHEN zeit_starttime < 7 THEN datum_starttime - 1_tag ELSE datum_starttime END)

- Ich habe mich jetzt nur auf starttime bezogen. Die picks scheinen ja immer recht kurz, die endtime kann eventuell auch über 7 Uhr hinaus reichen und noch zur Nachtschicht gehören.

Das wird grössenteils korrekt sein, aber bei Schichtüberschneidungen bzw. überstunden der Nachtschicht werden diese nicht mehr korrekt sein

- Dein Code hat ein paar generelle Schwächen: Die ganzen Zeitkonvertierungen nerven,

Leider ist das nicht zu umgehen oder? lasse ich es weg, sind die Zeiten immer um 2h verschoben - vermutlich wegen einer Datenbankeinstellung, auf die ich aber keinen Einfluss nehmen kann

- der Left Join auf pickingorders ist in deinem Beispiel zumindest unnötig, theoretisch könnten hier Datensätze aus picking mehrfach gezählt werden, wenn es mehrere pickingorders pro picking geben kann.

Ich habe 2-3 Spalten entfernt, da die keinen Einfluss auf die Logik für die gruppierung haben, aber in Realität brauche ich dann den join

- Auch verwendest du kein BETWEEN, das würde sich hier anbieten. Allerdings liebe ich Genauigkeit und bis <= 06:59:59 (das trifft ebenso BETWEEN) könnte z.B. bei einer höheren Genauigkeit im Zeitstempel Werte zwischen 06:59:59 und 07:00:00 nicht korrekt einschließen, daher dann doch bitte nur mit < 07:00:00 arbeiten.

wird am ende geändert zu between sysdate - 7 and sysdate. Für die Kontrolle habe ich es so gewählt, damit ich sicher einige zusammenhängende Tage zur Kontrolle habe

Nur mal so nebenbei: Wenn du schon alle Zeitzonen auf Zürich konvertierst, geht denn eine Nachtschicht immer bis 7 Uhr morgens in Zürich? Warum zählst du pickings nicht nach Ortszeit zum Datum?

Leider ist es eher dynamisch, die Schicht geht bi sungefähr 7 Uhr Morgens, aber je nach Auftragslage kann es sein, dass bereits um 05:00 Feierabend ist oder auch erst um 08:00 Uhr.
Da aber die Frühschicht imme rum 07:00 beginnt, kann ich ja nicht einfach sagen, alles vor 7 Uhr zähle ich zur Nachtshcicht und somit zum Vortag, da die Überschneidung der Schichten mir entweder Fehler gibt für die Frühschicht- oder die Nachtschicht.

 

[ukulele]

Leider ist es eher dynamisch, die Schicht geht bi sungefähr 7 Uhr Morgens, aber je nach Auftragslage kann es sein, dass bereits um 05:00 Feierabend ist oder auch erst um 08:00 Uhr.
Da aber die Frühschicht imme rum 07:00 beginnt, kann ich ja nicht einfach sagen, alles vor 7 Uhr zähle ich zur Nachtshcicht und somit zum Vortag, da die Überschneidung der Schichten mir entweder Fehler gibt für die Frühschicht- oder die Nachtschicht.

Dann ist das dein Kernproblem. Schichtende vor 5 Uhr ist natürlich unerheblich, aber nach 7 Uhr und damit Beginn einer neuen Schicht kann natürlich in dieser "einfachen" Version nicht funktionieren, es sei denn man kann mit der Ungenauigkeit leben.

Da keine Eckdaten vorliegen, wann wer seine Schicht beginnt, könnte man das ermitteln. Das wird allerdings ein bisschen Performance kosten, eventuell sollte das nicht live gemacht werden sondern irgendwie regelmäßig in eine eigene Tabelle geschrieben werden. Aber man kann ja erstmal testen.

Grundsätzlich könnte man das mit CTE lösen, dafür wäre aber interessant wie viele Zeilen (also picks) ein User in einer Schicht haben kann. Vielleicht geht es auch schneller und eleganter mit lag() / Lead(), ich muss mir da nachher mal Gedanken zu machen

 

[borukthedwarf]

Wir reden von Picks (Zeilen) um die 300 - 700 pro Schicht und vermutlich um die 120 verschiedene user.

Aber wenn ich es mir alles nach User und dann nach Zeiten ordne, kann ich mittels lag() eigentlich sagen wann eine Schicht fertig ist, und dann so ein Datum zuweisen und im Anschluss gruppieren?

 

[akretschmer]

Das wird grössenteils korrekt sein, aber bei Schichtüberschneidungen bzw. überstunden der Nachtschicht werden diese nicht mehr korrekt sein

dann nehme die gespeicherte Zeit und addiere/subtrahiere einen Offset dazu, so daß IMMER das auf den korrekten Tag fällt.

 

[ukulele]

So irgendwie fehlte mir das Mojo, hier mal MSSQL Code:

WITH picks(userid,starttime,finishtime,pickedamount_baseqty) AS (
    SELECT 'User1',cast('17.07.2023 22:05:05' AS DATETIME),cast('17.07.2023 22:05:16' AS DATETIME), 3 UNION ALL
    SELECT 'User1','17.07.2023 22:05:16','17.07.2023 22:06:01', 6 UNION ALL
    SELECT 'User1','17.07.2023 23:59:55','18.07.2023 00:00:04', 1 UNION ALL
    SELECT 'User1','18.07.2023 00:00:05','18.07.2023 00:01:01', 6 UNION ALL
        SELECT 'User1','18.07.2023 05:00:05','18.07.2023 05:01:01', 99 UNION ALL
        SELECT 'User1','18.07.2023 08:00:05','18.07.2023 08:01:01', 88 UNION ALL
        SELECT 'User1','18.07.2023 08:05:05','18.07.2023 08:05:01', 77 UNION ALL
    SELECT 'User2','18.07.2023 07:00:54','18.07.2023 07:01:32', 5 UNION ALL
    SELECT 'User2','18.07.2023 07:01:32','18.07.2023 07:02:02', 3
    ), t1 AS (
    SELECT    *,
            (    CASE
                WHEN    lag(finishtime) OVER (PARTITION BY userid ORDER BY starttime) IS NULL
                OR        datediff(hour,lag(finishtime) OVER (PARTITION BY userid ORDER BY starttime),starttime) > 2
                THEN    1
                ELSE    0
                END ) AS schichtwechsel
    FROM    picks
    ), t2 AS (
    SELECT    *,
            sum(schichtwechsel) OVER (PARTITION BY userid ORDER BY starttime) AS schicht 
    FROM    t1
    )
SELECT    userid,
        min(cast(starttime AS DATE)) AS datum,
        sum(pickedamount_baseqty) AS summe,
        schicht
FROM    t2
GROUP BY userid,schicht
ORDER BY userid,schicht

Ich habe drei Zeilen Testdaten ergänzt um so zu tun als hätte User1 immer wieder gearbeitet mit Lücken dazwischen. Beim aller ersten Datensatz jedes Users ist lag(finishtime) NULL, da beginnt immer die erste Schicht eines jeden Users. Wenn eine größere Lücke ~2h* zwischen zwei Picks liegt (datediff()), dann soll ein Schichtwechsel statt finden. Das ist die Kernidee und kann natürlich beliebig definiert werden. Das muss aber verschachtelt werden, t1 ermittelt die Schichtwechsel, t2 macht nochmal eine laufende Summe per Window-Funktion und zum Schluss wird gruppiert.

* Vorischt bei datediff(), ich weiß nicht genau wie es bei Oracle läuft. Bei MSSQL betrachtet datediff(hour,spalte1,spalte2) den Unterschied im Zeitanteil Stunden, nicht den Zeitunterschied in Stunden. 1:59 Uhr bis 2:01 Uhr ist ein Unterschied der Stunden von 1, 1:01 Uhr bis 1:59 Uhr ein Unterschied 0. Das kann schnell mal für Verwirrung sorgen, das richtig sauber zu machen sollte aber nicht das Problem sein.

Aktuell betrachtest der Code alle Datensätze, dann funktioniert das. Allerdings wirst du ein Problem bekommen wenn du die Datensätze zuerst eingrenzt, z.B. auf die letzten 14 Tage. Du fängst dann ggf. mitten in einer Schicht vom Vortag an. Am besten du nimmst dann 15 Tage als WHERE-Kriterum in t1, dann werden ggf. picks der ersten beiden Tage der Schicht vom ersten Tag zugeschrieben und am Ende beim gruppieren schneidest du dann noch den ersten Tag per WHERE-Kriterium weg, dann ist es ganz genau.