Horror Abfrage um die Lagerdauer herauszufinden - Hilfe!

[melih79]

Hallo zusammen,

ich wende mich an euch Experten, weil ich nicht mehr weiter weiß. Ich erstelle Abfragen mit dem SQL Management Studio 2016 und stehe vor einem Problem:

In der Datenbank befindet sich eine Tabelle „Lager“. Sie besitzt 4 Spalten. Diese Tabelle wird automatisch von einem Vorsystem befüllt. Sobald ein Teil im Lager eintrifft wird ein Datensatz erstellt:

Vorgangsnr	Artikelnr	Ein/Aus	Zeit
1001	89	E	2019-09-01 01:13:00

Und sobald dieses Teil das Lager wieder verlässt, wird ein Datensatz mit „A“ für Ausgang angelegt mit der selben Vorgangsnr vom Eingang:

Vorgangsnr	Artikelnr	Ein/Aus	Zeit
1001	89	A	2021-09-01 04:13:00

Somit befinden sich in einer Tabelle alle Eingänge „E“ und Ausgänge „A“. Die Eingänge und Ausgänge sind verknüpft durch die Vorgangsnr. Wenn das Teil das Lager nicht verlassen hat, dann befindet sich in der Tabelle kein Datensatz mit "A", sondern nur mit "E":

Vorgangsnr	Artikelnr	Ein/Aus	Zeit
1001	89	E	2021-09-01 01:13:00
1001	89	A	2021-09-01 04:13:00
1002	55	E	2021-09-03 14:13:00
1002	55	A	2021-09-03 15:13:00
1003	44	E	2021-09-05 15:00:00

Wir benötigen alle Datensätze die einen Ein und Ausgang haben und die Zeit, wie lange das Teil im Lager lag im Format (SS:MM):

Vorgangsnr	Artikelnr	Ein	Aus	Dauer
1001	89	2021-09-01 01:13:00	2021-09-01 04:13:00	03:00
1002	55	2021-09-03 14:13:00	2021-09-03 15:13:00	01:00

Dadurch, dass die Ein und Ausgänge sich in einer Tabelle befinden, bekomme ich es einfach nicht hin

Könnt ihr mir bitte helfen?!

VG
Melih

[Christian Marquardt]

Sollte in die Richtung gehen

select vorgangsnr, t1.zeit,t2.zeit from tabelle as t1
left join tabelle as t2 on t1.vorgangsnr=t2.vorgangsnr and t2.einaus='A'

[melih79]

Hallo Christian,

Vielen Dank für deinen Denkanstoß! Es hat mit folgender Abfrage geklappt:

SELECT a.[Vorgangsnr], a.[Artikelnr], a.[Zeit], b.[Zeit], DATEDIFF(MINUTE,a.[Zeit],b.[Zeit])
FROM [PPCAS_PROD].[TBL_LAGER] a
LEFT JOIN [PPCAS_PROD].[TBL_LAGER] b on a.[ Vorgangsnr]=b.[Vorgangsnr]
WHERE a.[Ein/Aus]='E' and b.[Ein/Aus]='A'

Somit erhalte ich schon mal die korrekt Differenz als Zahl. Z.B 70 Min.

Wie kann ich im letzten Schritt die 70 "Min" in dieses Format bringen: 1:10 Min ?

[Christian Marquardt]

https://social.msdn.microsoft.com/Fo...um=transactsql

[melih79]

Hat geklappt vielen Dank für deine Unterstützung!!!
Gruß
Melih

[melih79]

Christian,

macht es eigentlich mehr Sinn, die Daten aus dem Vorsystem beim Import auf die Datenbank auf 2 Tabellen zu splitten ? In der einen Tabelle wären alle "Eingangs" Datensätze und in der zweiten Tabelle alle "Ausgangs" Datensätze.

Dann könnte man problemlos eine "Fact" Tabelle erstellen, wo dann verschieden KPIS liegen zu jeder vorhandenen Vorgangsnr.

Was meinst du das?

[Christian Marquardt]

Sehe da kein Unterschied, ob der Join auf die gleiche oder eine andere Tabelle geht.
Ein Unterschied wäre, ob in einer Tabelle E und A in einem Datensatz zusammen gefasst werden

Vorgangsnr | Artikelnr | EinZeit | AusZeit

[melih79]

Genau das meine ich. Dann hätte man einen Datensatz zu einer bestimmten Vorgangsnr. Und in diesem Datensatz wären die E und A. Man müsste nicht joinen und könnte in dieser Tabelle dann auch eine Spalte "Dauer" erweitern, wo dann E-A = Dauer des Artikels auf dem LAger
Vorgangsnr | Artikelnr | EinZeit | AusZeit | Dauer

Wir bauen unsere Reports mit Power BI . Dann müsste ich nur diese eine Tabelle knüpfen.

[Christian Marquardt]

Dann würde ich das gleich mit der Berechnung der Dauer so machen

[melih79]

Ok, werde das an die IT weitergeben. Danke