3-fache Zeitüberschneidung

[aberglan]

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

[Sir Rufo]

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.

[Valle]

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

[DeddyH]

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?

[p80286]

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

[Mikkey]

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

[Valle]

Zitat von DeddyH:

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

[Furtbichler]

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).