SQL-Abfrage um bestimmten Zeitraum Spaltenabhängig zu betrachten

[7ommak]

Hallo zusammen,

da es mein erster Beitrag in diesem Forum ist, bin ich auch über inhaltliche Korrektur-/Verbesserungsvorschläge dankbar.

Folgendes Bild zeigt einen Ausschnitt aus einer Tabelle 'z_prod_log' die Schichtmeldungen aus einer Produktion erfasst. Die Schichtmeldungen werden zu jedem Auftrag (ord_no) erfasst und beginnen immer mit einem Start-Ereignis (10) und enden (so sollte es jedenfalls sein) mit einem End-Ereignis (50). Zum Start-Ereignis wird nur eine Start-Zeit angegeben und zum End-Ereignis zur eine End-Zeit. Alle anderen Ereignisse die ziwschen Start und Ende stattfinden haben immer ein Start- und ein End-Ereignis.

Ich benötige nun für einen angegebenen Zeitraum (z.B. 16.03.2017 - 22.03.2017) alle Ereignisse (Events) die in diesem Zeitraum erfasst wurden. Das Problem meiner Abfrage ist, dass ich zwar alle Ereignisse bekomme die in diesem Zeitraum erfasst wurden, allerdings auch End-Ereignisse die außerhalb des Zeitraumes liegen UND dessen zugehörigen Ereignisse nicht innerhalb dieses Zeitraumes liegen.
Ich habe nun zwei Probleme, die ich nicht mit der Abfrage hinbekomme:

1.
- Start-Ereignis von einem Auftrag liegt vor dem Zeitraum
- End-Ereignis von diesem Auftrag liegt hiner dem Zeitraum
-> Ich benötige zu diesem Auftrag dann trotzdem alle Ereignisse (auch Start- und End-Ereignis und ein Ereignis mit Event-ID 90 oder 91, welches keine Start- und/oder End-Zeit besitzt)
2.
- Es gibt für einen Auftrag zwar ein Start-Ereignis (10) das den oben genannten Kriterien entspricht, aber es wurde noch kein End-Ereignis (50) erfasst
-> Es darf zu diesem Aufrag dann kein Ereignis selektiert werden, da der Auftrag noch nicht abgeschlossen wurde

Ich bin um jede Hilfe sehr dankbar. Ich habe lange nichts mehr in SQL gemacht und sehe da auch eindeutig nicht meine Stärken drin :-D

Gruß
Thomas

 

[ukulele]

Also ganz eindeutig ist das noch nicht. Grundsätzlich würde ich so vorgehen:

SELECT   *
FROM   z_prog_log
WHERE   ord_no IN (

SELECT   ord_no
FROM   z_prog_log
WHERE   event_id NOT IN ( 10,50 )
AND   (   date_start BETWEEN '2017-03-16 00:00:00.000' AND '2017-03-22 23:59:59.997'
OR     date_start BETWEEN '2017-03-16 00:00:00.000' AND '2017-03-22 23:59:59.997' )

     )

Ich suche im Subselect alle event_ids die ein tatsächliches Ereignis sind (also kein Start oder Ende) und den Zeitraum schneiden. Dann gebe ich zu dieser Liste mit event_ids alle Ereignisse aus, die damit in Zusammenhang stehen.

Jetzt werden allerdings auch Zeiträume erfasst die noch kein Enddatum haben. Das könnte ich noch so ergänzen:

SELECT   z.*
FROM   z_prog_log z
WHERE   z.ord_no IN (

SELECT   ord_no
FROM   z_prog_log
WHERE   event_id NOT IN ( 10,50 )
AND   (   date_start BETWEEN '2017-03-16 00:00:00.000' AND '2017-03-22 23:59:59.997'
OR     date_start BETWEEN '2017-03-16 00:00:00.000' AND '2017-03-22 23:59:59.997' )

     )
AND EXISTS (

SELECT   1
FROM   z_prog_log
WHERE   z_prog_log.event_id = 50
AND     z_prog_log.ord_no = z.ord_no

     )

Das geht aber eventuell auch eleganter mit einem Join.

Außerdem werden keine Zeiträume erfasst die ein Anfangsdatum vor dem Zeitraum, ein Enddatum nach dem Zeitraum und kein Ereignis im Zeitraum haben. Sind die relevant? Dann würde ich eventuell ganz anders anfangen.

 

[7ommak]

Hallo ukulele,

erst einmal Danke für deine Hilfe. Einen ähnlichen Ansatz hatte ich auch schon, die Abfrage im Bild sollte auch lediglich Datensätze zeigen an denen
ich mein Problem gut erklären kann.

Also ganz eindeutig ist das noch nicht.

Außerdem werden keine Zeiträume erfasst die ein Anfangsdatum vor dem Zeitraum, ein Enddatum nach dem Zeitraum und kein Ereignis im Zeitraum haben. Sind die relevant? Dann würde ich eventuell ganz anders anfangen.

Ich versuche mich nochmal genauer auszudrücken. Ich werde aus den Daten den Verfügbarkeitsfaktor der Produktionsanlagen für eine OEE-Berechnung ermitteln. Dafür benötige ich für einen gegebenen Zeitraum alle Ereignisse die innerhalb dieses Zeitraums liefen. Dazu gehören somit vier verschiedene "Typen":

• Aufträge die innerhalb des Zeitraums gestartet (10), aber noch nicht beendet (50) wurden
• Aufträge die innerhalb des Zeitraums beendet (50), aber bereits vor dem Zeitraum gestartet (10) wurden
• Aufträge die innerhalb des Zeitraums gestartet (10) und beendet (50) wurden
• Aufträge die vor dem Zeitraum gestartet und nach dem Zeitraum beendet werden. Dabei ist es egal, ob es noch
  andere Ereignisse gibt oder nicht, und wenn ja, ob diese in dessen Start- und Endzeiten innerhalb des Zeitraums liegen oder nicht

Allerdings konnte ich das Problem nun lösen, der EXISTS Befehl hat gefehlt.

SELECT *
FROM Z_PROD_LOG L
WHERE EXISTS (
    SELECT *
    FROM Z_PROD_LOG X
    WHERE X.event_id = 10
        AND X.date_start < '24.03.2017'
        AND X.ord_no = L.ord_no
)
AND EXISTS (
    SELECT *
    FROM Z_PROD_LOG X
    WHERE X.event_id = 50
        AND X.date_end >= '16.03.2017'
        AND X.ord_no = L.ord_no
)
AND L.cost_center BETWEEN '1148001' AND '1148003'
ORDER BY L.ord_no

Gruß
Thomas