Delphi-PRAXiS - 3-fache Zeitüberschneidung

Delphi-PRAXiS (https://www.delphipraxis.net/forum.php)

- Datenbanken (https://www.delphipraxis.net/15-datenbanken/)

- - 3-fache Zeitüberschneidung (https://www.delphipraxis.net/176393-3-fache-zeitueberschneidung.html)

3-fache Zeitüberschneidung

Guten Tag,

Ich wollte mal fragen ob jemand von euch schon einmal eine Query welche eine Zeitüberschneidung von 3 verschiedenen Tabellen prüft erstellt hat?

Es handelt sich um Dienstlisten in deenen sich Mitglieder eintragen können wann sie Dienst haben. Ich habe 3 Tabellen für 3 verschiedene Dienst welche mit den Feldern idDate, idStart, idEnde beginnen. Danach kommen noch Felder 3 Felder dtP1 bis dtP3 in deenen die Mitglieder für die verschiedenen Schichten stehen. idDate in ein Datumsfeld und idStart rsp idEnde sind Uhrzeitenfelder.

In jeder der 3 Tabellen können die Zeiten der Schichten verschieden sein und ich möchte gerne überprüfen welche der Schichten in den drei Tabellen sich überschneiden um später zu kontrollieren ob ein Mitglied zeitgleich auf mehreren Dienstschichten eingetragen ist.

Für zwei Tabellen habe ich diese Kontrolle hinbekommen, komm jedoch jetzt nicht richtig weiter die dritte Tabelle einzubinden. Hier meine Query für zwei Tabellen

Code:

			SELECT a.idDate, IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) AS astart, IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) AS aend, IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart ) AS bstart, IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd ) AS bend

FROM tblperma_a1 AS a, tblperma_a2 AS b

WHERE (

a.idTrancheStart = b.idTrancheStart

AND a.idTrancheEnd = b.idTrancheEnd

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) > IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) = IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) < IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) = IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) = IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) > IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) = IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) < IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) > IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) > IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

OR (

IF( a.idTrancheStart = '00:00:00', '24:00:00', a.idTrancheStart ) < IF( b.idTrancheStart = '00:00:00', '24:00:00', b.idTrancheStart )

AND IF( a.idTrancheEnd = '00:00:00', '24:00:00', a.idTrancheEnd ) > IF( b.idTrancheEnd = '00:00:00', '24:00:00', b.idTrancheEnd )

AND a.idDate = '2013-09-02'

AND b.idDate = '2013-09-02'

)

ORDER BY a.idTrancheStart

Hat jemand vielleicht eine Idee? Vielen Dank

AW: 3-fache Zeitüberschneidung

Erkläre doch mal, wozu du 3 Tabellen hast.

Ich hätte das in eine Tabelle gepackt und ein Feld für den Diensttyp.

Ansonsten vorher die Tabellen joinen und darauf die Abfrage loslassen.

AW: 3-fache Zeitüberschneidung

Hi,

ich hab das jetzt nicht getestet, weil das Anlegen der Demodaten mir zu lange dauern würde, aber kannst du das nicht einfach so machen?

Code:

			SELECT * FROM a 

WHERE EXISTS(SELECT id FROM b WHERE b.start BETWEEN a.start AND a.end)

OR EXISTS(SELECT id FROM c WHERE c.start BETWEEN a.start AND a.end)

Wenn du das per-Mitarbeiter machen willst, kannst du die beiden Subqueries sicherlich um ein "AND a.mitarbeiterID = b|c.mitarbeiterID" erweitern.

Interessant wäre noch, wie groß die Tabelle so ist oder werden kann. Es gibt sicherlich noch performantere Lösungen, die ohne 2 Subqueries auskommen.

Was deine vielen IFs da machen versteh ich auch nicht so recht.

Edit:// Weitere Möglichkeit, ohne Subquery, daher vermutlich schneller:

Code:

			SELECT a.* FROM a, b, c

WHERE  b.start BETWEEN a.start AND a.end

OR c.start BETWEEN a.start AND a.end

Hab's mit 3 einfachen Tabellen und insgesamt 4 Einträgen auch mal getestet.

Liebe Grüße,
Valentin

AW: 3-fache Zeitüberschneidung

Und was ist, wenn ein Termin vorher beginnt, aber innerhalb des anderen endet? Oder einer einen anderen vollständig überlappt, also vorher beginnt und nachher endet?

AW: 3-fache Zeitüberschneidung

Ich denke das kann man schon mit dem

Code:

WHERE b.start BETWEEN a.start AND a.end

erschlagen.
Nur muß B immer aus allen 3 Tabellen gefüttert werden.
(wer ist eigentlich auf diese "Schnapsidee" mit den 3 Tabellen gekommen?)

Gruß
K-H

AW: 3-fache Zeitüberschneidung

Damit das mit den Abfragen richtig funktioniert, muss eine Tabelle zur "Führung" vorhanden sein:

Code:

			Select Mitarbeiter m

 outer Join a on a.Mitarb=m.id

 outer Join b on b.Mitarb=m.id

 outer Join c on c.Mitarb=m.id

where a.start between b.start and b.end or

      b.start between a.start and a.end or

      a.start between c.start and c.end or

      b.start between a.start and a.end or

      b.start between c.start and c.end or

      c.start between b.start and b.end

Gruß, Mikkey

AW: 3-fache Zeitüberschneidung

Zitat:

Zitat von DeddyH (Beitrag 1226897)

Und was ist, wenn ein Termin vorher beginnt, aber innerhalb des anderen endet? Oder einer einen anderen vollständig überlappt, also vorher beginnt und nachher endet?

Stimmt, die fehlen.

Wenn wir zwei Intervalle A und B haben, dann schneiden sie sich aber doch immer, wenn entweder A.start in B oder B.start in A, oder? Fehlt noch was?

Mikkey hat schon eine schöne gepostet, finde ich. Dass er direkt die entsprechenden Mitarbeiter selektiert finde ich auch schöner, als willkürlich Tabelle A zu verwenden.

Liebe Grüße,
Valentin

AW: 3-fache Zeitüberschneidung

Wenn ich mal wieder unterschiedliche Tabellen vor mir habe (was sehr häufig vorkommt, man sollte die Entwickler echt peitschen, die so einen Dreck verzapfen), die man aber eigentlich über einen Kamm scheren muss, mache ich in etwa Folgendes:

Code:

			select 'Tabelle' as Tabelle, Feld1, Feld2 ... from Tabelle

union

select 'AndereTabelle' as Tabelle, FeldX, FeldY ... from AndereTabelle

...

Das ganze am Besten hinter einer View. Ich unifiziere also meine heterogenen Daten, um anschließend bequem mit ihnen arbeiten zu können. Die Spalte 'Tabelle' habe ich, um hinterher zu wissen, wo das Zeugs herkommt.

Mit dieser View kann ich dann so arbeiten, also ob die DB nicht versaut wäre. Is nur lahm (kann man aber manchmal verbessern, Stichwort materialized view).