Kummulierte Lagerbestände pro Tag vor Bekanntwerden des Bestands

[Erwin Pflaumenmus]

Hallo zusammen,

bei dem folgenden Problem habe ich irgendwo einen Denkfehler und komme nicht weiter:

Ich brauche eine Auswertung der Palettenanzahl, die Pro Tag in einem Regal stand.
Dieser Zeitraum liegt zwischen Neuanlage der Wareneingangsposition im Vor-WE (w.time_neu) und Neuanlage der Entsprechenden Wareneingangsbewegung des End-WE (b.time_neu). Beim Vor-WE werden noch keine Ladeeinheiten gebildet.
Gezählt wird die Anzahl der eindeutigen Ladeeinheiten b.typ_le_1_an = 'PAL' beim End-WE.
Die Anzahl ist somit erst zum Zeitpunkt des End-WE bekannt, wo die Paletten nicht mehr im Regal stehen und soll rückwirkend für die Tage seit dem Vor-WE gezählt werden.

Beispielsituation:
Es gibt 2 Wareneingänge, die am 30.05. gebucht wurden.
Zu WE A wurden am 30.05. 4 Paletten gebildet.
Zu WE B wurden am 02.06. 12 Paletten gebildet.
Es waren demnach am 30.05. 4+12 Paletten im Regal und bis zum 02.06. noch 12 Paletten.
Meine Abfrage (abgefragt am 02.06.) gibt mir nur für den 30.05. die 16 Paletten aus, für die anderen Tage 0.

WITH min_max_dates AS (
SELECT
TO_CHAR(MIN(w.time_neu), 'YYYYMMDD') AS min_time_str,
TO_CHAR(MAX(w.time_neu), 'YYYYMMDD') AS max_time_str
FROM
bewegungen b
JOIN
wepos w ON w.nr_we = b.nr_we_an
)
SELECT
TO_CHAR(Datum, 'YYYYMMDD') AS Datum,
COUNT(DISTINCT b.nr_le_1_an) AS PAL_im_Regal
FROM (
SELECT
TO_DATE(min_max_dates.min_time_str, 'YYYYMMDD') + LEVEL - 1 AS Datum
FROM
min_max_dates
CONNECT BY
TO_DATE(min_max_dates.min_time_str, 'YYYYMMDD') + LEVEL - 1 <= TO_DATE(min_max_dates.max_time_str, 'YYYYMMDD')
)
LEFT JOIN bewegungen b ON TO_CHAR(b.time_neu, 'YYYYMMDD') = TO_CHAR(Datum, 'YYYYMMDD')
JOIN wepos w ON w.nr_we = b.nr_we_an
WHERE
b.art_bew IN ('WEBA', 'WEGS', 'WEPF', 'WEXD', 'WERE', 'WEQS', 'WEPM', 'WEVP', 'WEVE')
AND b.typ_le_1_an = 'PAL'
AND b.nr_we_pos_an = w.nr_we_pos
AND TO_CHAR(b.time_neu, 'YYYYMM') >= '202505' -- kommt später weg, soll Anzahl Ergebnisse reduzieren
GROUP BY TO_CHAR(Datum, 'YYYYMMDD')
HAVING COUNT(DISTINCT b.nr_le_1_an) > 0

 

[ukulele]

Deine Beschreibung ist gut, die verstehe ich. Der Oracle Code ist etwas verwirrend, das bin ich nicht gewohnt. Vor allem müsste doch in Zeile 20 hinter der Klammer ein Tabellenalias stehen, zumindest würde MSSQL da meckern. Aber egal, ich glaube, ich verstehe dein Grundsätzliches Problem, ich versuche das mal, in Worte zu fassen

Im Prinzip hast du einen Zeitstrahl mit Zustandsveränderungen, also Zugänge und Abgänge aus deinem Lager. Jede Zustandsveränderung wird durch einen Datensatz repräsentiert. Wenn du jetzt alle Datensätze gruppierst, wird jeder Datensatz immer nur Bestandteil einer Gruppe sein (und damit auch einer Summe). Dein WE B kann also entweder in die Summe vom 30.05. oder in die Summe vom 02.06. eingehen.

Um das zu lösen, legst du eine Tabelle min_max_dates an, in der zwar nicht alle Tage des Zeitstrahls stehen, aber alle, wo eine Bestandsveränderung statt findet. Darauf joinst du dann erneut jede Bewegung, so das Bewegungen mehrfach in die Gruppierung eingehen. Jetzt kommt ein Teil, den ich nicht verstehe: CONNECTED BY und LEVEL ist scheinbar für Rekursion. Die braucht es aber nach meinem Verständnis gar nicht. Kannst du nicht einfach auf deinen Zeitstrahl jede Position joinen, die zu dem Zeitpunkt im Lager war und das dann aggregieren?

Eine andere, eventuell sogar bessere, Möglichkeit wären Window-Functions. Einfach alle Daten in eine Reihe bringen und eine laufende Summe bilden die sich entweder erhöht durch einen Wareneingang oder verringert, durch einen Warenabgang.

https://blogs.oracle.com/sql/post/cumulative-running-total-of-previous-rows-with-sql

 

[Erwin Pflaumenmus]

Danke für die Antwort, das hat mir nochmal geholfen das ganze besser zu verstehen, da ich nie SQL gelernt habe. Ich habe aber ein einigermaßen gutes Verständnis für Programmiersprachen und bastel mir mithilfe von vorhandenen Abfragen und Google was eigenes zusammen

Zu deinen Anmerkungen:
Wie du schon sagtest, umfasst der Zeitstrahl nur Tage, an denen es Bestandsveränderungen gab.
Für den Zweck der Auswertung ist das an sich ausreichend. Ich finde es aber übersichtlicher wenn auch Tage mit drin stehen, an denen es zwar keine Bestandsveränderung gab, aber dennoch Paletten unberührt im Regal standen.
Daher nutze ich hier CONNECTED BY und LEVEL um einen vollständigen Zeitstrahl zu generieren.

Ein Tabellenalias ist hier nicht nötig, weil die Subquery direkt auf die Felder "min_time_str" und "max_time_str" verweist, die innerhalb der CTE "min_max_dates" eindeutig sind.

 

[ukulele]

Ja das CONNECTED BY ist Oracle, das habe ich halt nicht. Bei PostgreSQL gibt es generate_series(), das ist dann ziemlich praktisch und einfach. Bei MSSQL, was ich nutze, könnte man mit Rekursion das ganze natürlich auch machen, ist aber syntaktisch ganz anders.

 

[castorp]

SQL Server hat seit der Version 2022 auch ein generate_series()

GENERATE_SERIES (Transact-SQL) - SQL Server

Generates a series of numbers within a given interval. The interval and the step between series values are defined by the user.

learn.microsoft.com

 

[dabadepdu]

SQL Server hat seit der Version 2022 auch ein generate_series()

Schau an!
Ein schönes Beispiel für die Kraft von Open-Source-Software. Sie haben es nicht mal anders genannt!
Vielleicht verhält es sich wenigstens anders, haha.