Termintabelle: Überlappende Termine zusammenfassen

[David Aurelio]

Guten Abend allerseits,

der verwendete Datenbankserver ist MySQL Version 4.1

ich habe eine Tabelle mit Terminen, die folgendermaßen aufgebaut ist:
+------------+----------------------+
| Field      | Type                 |
+------------+----------------------+
| id         | int(10) unsigned     |
| date       | date                 |
| begin      | time                 |
| end        | time                 |
+------------+----------------------+

Die Termine liegen jeweils nur an einem Tag, ich könnte natürlich die begin/end auch als DATETIME-Typen anlegen.

Nun zu meinem Problem:
Ich möchte gerne sich überschneidende Termine als einen einzigen zusammenfassen. Ist das möglich, z.B. mit einem "GROUP BY"-Statement?
Falls ja wäre ein Denkanstoss sehr nett.

Noch besser wäre es, direkt die "Leerräume" zwischen den Terminen selektieren zu können, aber ich gehe mal davon aus, das dies mit MySQL direkt nicht möglich ist.

Um die Sache etwas plastischer zu gestalten, habe ich mal ein paar Beispieldatensätze angehängt; jeweils zwei Sätze überschneiden sich.
+----+------------+----------+----------+
| id | date       | begin    | end      |
+----+------------+----------+----------+
|  1 | 2006-05-30 | 09:00:00 | 09:30:00 |
|  2 | 2006-05-30 | 09:15:00 | 09:35:00 |
|  3 | 2006-05-30 | 09:45:00 | 10:05:00 |
|  4 | 2006-05-30 | 09:45:00 | 10:10:00 |
|  5 | 2006-05-30 | 10:30:00 | 10:40:00 |
|  6 | 2006-05-30 | 10:40:00 | 10:45:00 |
+----+------------+----------+----------+
Ergebnis des SELECT-Statements wäre idealerweise:
+------------+----------+----------+
| date       | begin    | end      |
+------------+----------+----------+
| 2006-05-30 | 09:00:00 | 09:35:00 |
| 2006-05-30 | 09:45:00 | 10:10:00 |
| 2006-05-30 | 10:30:00 | 10:45:00 |
+------------+----------+----------+

Vielen Dank für eure Hilfe,
David

[wahsaga]

hi,

Ich möchte gerne sich überschneidende Termine als einen einzigen zusammenfassen. Ist das möglich, z.B. mit einem "GROUP BY"-Statement?

Nein, mit GROUP BY wohl nicht.

Eher schon mit Subselects oder JOINs - ein Termin, der vor einem anderen beginnt, aber in ihm endet, bzw. vice versa, überschneidet sich mit diesem.

Aber da lass mal lieber die paar wirklichen DB-Experten, die wir hier haben, sich die Köpfe zerbrechen - mir ist das im Augenblick 'ne Nummer zu hoch :-)

gruß,
wahsaga

[Bio]

Sup!

Wann möchtest Du die Daten zusammenfassen? Bei der Ausgabe? Bei der Eingabe eines kollidierenden Termins?

Ich würde verhindern, dass überhaupt solche Termine eingetragen werden können, denn wenn es zwei Termine gibt hast Du das Problem, aus zwei, möglicherweise verschiedenen Terminen einen "Gesamt-Termin" zu machen. Also wenn ich z.B. von 10:00 bis 10:30 mit Thomas Anders an der Eisdiele in Bonn verabredet bin und von 10:15 bis 11:00 mit Horst Köhler an der Frittenbude am Brandenburger Tor, habe ich dann einen Gesamt-Termin von 10:00 bis 11:00 mit Thomas Köhler in der Frittendiele in Salzgitter?

Gruesse,

Bio

[wahsaga]

hi,

Also wenn ich z.B. von 10:00 bis 10:30 mit Thomas Anders an der Eisdiele in Bonn verabredet bin und von 10:15 bis 11:00 mit Horst Köhler an der Frittenbude am Brandenburger Tor,

• dann hättest du auf jeden Fall einen sehr interessanten Bekanntenkreis. (Oder wärst Reporter bei BILD.)

habe ich dann einen Gesamt-Termin von 10:00 bis 11:00 mit Thomas Köhler in der Frittendiele in Salzgitter?

Erstmal hättest du dann ein Problem mit mindestens einer der Dimensionen Raum oder Zeit.
(Was dich aber als BILD-Reporter kaum stören dürfte - denn über mindestens einen der beiden Gesprächspartner würdest du dir einfach was zusammenlü^H^Hreimen.)

scnr,
wahsaga

[Stefan E.]

Hallo Ritter,

Ich würde verhindern, dass überhaupt solche Termine eingetragen werden können, denn wenn es zwei Termine gibt hast Du das Problem, aus zwei, möglicherweise verschiedenen Terminen einen "Gesamt-Termin" zu machen. Also wenn ich z.B. von 10:00 bis 10:30 mit Thomas Anders an der Eisdiele in Bonn verabredet bin und von 10:15 bis 11:00 mit Horst Köhler an der Frittenbude am Brandenburger Tor, habe ich dann einen Gesamt-Termin von 10:00 bis 11:00 mit Thomas Köhler in der Frittendiele in Salzgitter?

wichtig zu wissen ist, dass Du zwischen 11:00 und 12:00 keinen Termin
hast, weil der nächste Gesamttermin erst wieder 12:00 Uhr beginnt.
Möglich wäre z.Bsp. eine Anwendung, in die alle Fritten-, Eisdielen-
und Sonstwas-Termine eingetragen werden können, welche man wahlweise
wahrnehmen kann und jetzt möchte ich wissen, wann auf jeden Fall alle
Teilnehmer keinen Termin haben werden. Die Ermittlung der "Gesamt-
termine" erscheint mir auch relativ sinnfrei.

Viele Grüße,
Stefan

[David Aurelio]

Hallo Bio,

Wann möchtest Du die Daten zusammenfassen? Bei der Ausgabe? Bei der Eingabe eines kollidierenden Termins?

Bei der Ausgabe. Die Termine existieren, mich interessiert aber nur, "wann noch Zeit ist".

Ich würde verhindern, dass überhaupt solche Termine eingetragen werden können, denn wenn es zwei Termine gibt hast Du das Problem, aus zwei, möglicherweise verschiedenen Terminen einen "Gesamt-Termin" zu machen. Also wenn ich z.B. von 10:00 bis 10:30 mit Thomas Anders an der Eisdiele in Bonn verabredet bin und von 10:15 bis 11:00 mit Horst Köhler an der Frittenbude am Brandenburger Tor, habe ich dann einen Gesamt-Termin von 10:00 bis 11:00 mit Thomas Köhler in der Frittendiele in Salzgitter?

Konkret geht es darum, online mit einem Dienstleister Termine vereinbaren zu können, die von dessen interner Software synchronisiert werden.
Während der Dienstleister selbst die Möglichkeit haben soll, kollidierende Termine festzulegen, sollen "Online-Bucher" nur freie Termine zur Auswahl haben.

Mir ist es daher egal, wann welche Termine liegen und möglicherweise kollidieren, mich interessiert die noch freie Zeit.

Mein Denkansatz ist daher, festzustellen, wann Zeit nicht frei ist und daraus auf freie Zeit zu schließen.
Möglicherweise ist das zu kompliziert gedacht, aber dafür frage ich ja nach ;)

schönen Gruß, David

[Stefan E.]

Hallo David,

Noch besser wäre es, direkt die "Leerräume" zwischen den Terminen selektieren zu können, aber ich gehe mal davon aus, das dies mit MySQL direkt nicht möglich ist.

Um die Sache etwas plastischer zu gestalten, habe ich mal ein paar Beispieldatensätze angehängt; jeweils zwei Sätze überschneiden sich.
+----+------------+----------+----------+
| id | date       | begin    | end      |
+----+------------+----------+----------+
|  1 | 2006-05-30 | 09:00:00 | 09:30:00 |
|  2 | 2006-05-30 | 09:15:00 | 09:35:00 |
|  3 | 2006-05-30 | 09:45:00 | 10:05:00 |
|  4 | 2006-05-30 | 09:45:00 | 10:10:00 |
|  5 | 2006-05-30 | 10:30:00 | 10:40:00 |
|  6 | 2006-05-30 | 10:40:00 | 10:45:00 |
+----+------------+----------+----------+

break_start=end
existiert, wenn es keinen Datensatz gibt, bei dem begin<=break_start
AND end>break_start zutrifft.

break_end=begin
existiert, wenn es keinen Datensatz gibt, bei dem begin<break_end
AND end>=break_end zutrifft.

Vielleicht ist da jetzt auch ein ganz grober Denkfehler drin, aber
auf den ersten Blick würde ich behaupten, dass man damit die Zeit
der jeweiligen Pausen ermitteln kann.

Viele Grüße,
Stefan

[David Aurelio]

Hallo Stefan,

Vielleicht ist da jetzt auch ein ganz grober Denkfehler drin, aber
auf den ersten Blick würde ich behaupten, dass man damit die Zeit
der jeweiligen Pausen ermitteln kann.

Ich werde das gleich (oder vielleicht auch erst morgen früh ;) ) mal versuchen, umzusetzen, und melde mich dann wieder.
Danke Dir,
David

[Ilja]

yo,

Ich möchte gerne sich überschneidende Termine als einen einzigen zusammenfassen.

das sind zwei schritte. erst einmal musst du die termine herausbekommen, die sich überlappen. das macht man, indem man mit einem kleinen trick arbeitet. du vergleichst den startwert des einen datensatzes mit den endwert eines anderen datesnatzes und umgekehrt und das alles über einen selfjoin.

der zweite schritt ist, den jeweils kleineren, bzw. größeren wert zu nehmen, je nachdem, ob es sich um star- oder endwert handelt. das ganze noch mit einem DISTINCT ausgestattet, um doppelte datensätze rauszufiltern und voila...

SELECT DISTINCT tab1.date,
IF(tab1.begin <= tab2.begin, tab1.begin, tab2.begin) AS 'Startwert'
IF(tab1.end >= tab2.end, tab1.end, tab2.end) AS 'Endwert'
FROM termine AS tab1, termine AS tab2
WHERE tab1.id <> tab2.id
AND tab1.begin <= tab2.end
AND tab1.end >= tab2.begin

einfach mal ausprobieren und schauen, ob es noch was nachzubessern gibt.

Noch besser wäre es, direkt die "Leerräume" zwischen den Terminen selektieren zu können, aber ich gehe mal davon aus, das dies mit MySQL direkt nicht möglich ist.

darüber mache ich mir gedanken, wenn die erste abfrage klappt.

Ilja

[David Aurelio]

Hallo Ilja,

einfach mal ausprobieren und schauen, ob es noch was nachzubessern gibt.

Bis auf das fehlende Komma nach der zweiten Zeile gibt es nichts nachzubessern. Das funktioniert wunderbar.
Ich hatte bis heute noch nichts mit den MySQL-"Control Flow Functions" gemacht, wieder etwas dazu gelernt.
Vielen Dank und schönen Gruß,
David

[Ilja]

yo,

Bis auf das fehlende Komma nach der zweiten Zeile gibt es nichts nachzubessern. Das funktioniert wunderbar.

da lag mal wiede der fehler im detail, aber gut, dass du in gleich gefunden hast. ich muss meine abfrage leider kritisieren, sie klappt wohl nur, wenn es nur zwei termine gibt, die sich überlappen. das ist also keine optimale lösung. es wird doch komplizierter. muss darüber mal eine nacht schlafen. um am besten ist, wir versuchen dann lieber gleich die freiräume auszugeben.

Ilja

[Ilja]

yo,

neuer tag neues glück. wie gesagt, müssen wir die abfrage ein wenig umstellen, auch hier kommen dann korrelierende unterabfragen zum einsatz. ich habe sogar noch einen zweiten denkfehler gefunden, die bedingung, welche die datensätze mit der gleichen id ausschließt, muss raus. ich bin zu stark von deinen beispieldaten ausgegangen.

SELECT DISTINCT tab1.date, MIN(tab1.begin), MAX(tab1.end)
FROM termine AS tab1
WHERE tab1.id IN
 (SELECT tab2.id
  FROM termine AS tab2
  WHERE tab1.begin <= tab2.end
  AND tab1.end >= tab2.begin
 )
GROUP BY tab1.date
ORDER BY tab1.date

auf wunsch kann man nur einen speziellen tag auswählen, indem man in der WHERE Klausel einen entsprechenden Tag setzt. ich hoffe mal, das tut es nun.

Ilja

[David Aurelio]

Hallo Ilja,
leider gibt das pro Datum nur einen Satz zurück, mit dem Anfang des jeweils ersten und dem Ende des jeweils letzten Termins.

Ich habe gerade noch folgendes ausprobiert:

SELECT a.date AS `Datum`,  
       (  
        SELECT MIN(b.begin) FROM termine AS `b`  
          WHERE b.begin >= a.begin  
            AND b.end >= a.end  
            AND b.begin <= a.end  
            AND b.date = a.date  
       ) AS `Anfang`,  
       (  
        SELECT MAX(c.end) FROM termine AS `c`  
          WHERE c.begin >= a.begin  
            AND c.end >= a.end  
            AND c.begin <= a.end  
            AND c.date = a.date  
       ) AS `Ende`  
  FROM termine AS `a`  
  GROUP BY `Ende`  
  ORDER BY a.date, a.begin

Das funtkioniert aber auch nur bei zwei überlappenden Terminen.
Man bräuchte ein(e) 'WHILE'-Statement/-Funktion oder etwas in der Art, da man sonst pro potentieller Überlappung ein weiteres Subselect einbauen müsste.

Unglaublich, wie schwer eine auf den ersten Blick so einfach erscheinende Aufgabe teilweise umzusetzen ist.

schöne Grüße,
David

[Ilja]

yo,

leider gibt das pro Datum nur einen Satz zurück, mit dem Anfang des jeweils ersten und dem Ende des jeweils letzten Termins.

das ist logisch und liegt am GROUP BY, da müssen wir also noch mal ran.

SELECT DISTINCT MIN(tab.date), MIN(tab1.begin), MAX(tab1.end)
FROM termine AS tab1
WHERE tab1.id IN
 (SELECT tab2.id
  FROM termine AS tab2
  WHERE tab1.begin <= tab2.end
  AND tab1.end >= tab2.begin
 )

schau mal,ob es nun geht. wenn nicht, muss ich mir mal doch eine beispieldatenbank machen und ein wenig "rumfummeln".

Ilja

[David Aurelio]

schau mal,ob es nun geht.

Das (der?) Subselect selektiert alle Termine, die später liegen, als der selektierte Termin. Dadurch kommt immer nur ein Satz als Ergebnis raus.
Das Problem ist, dass wenn man der Selektionsbedingung AND tab2.begin <= tab1.end hinzufügt, nur "direkte" Überlappungen berücksichtigt werden, aber nicht z.B. drei direkt hintereinanderliegende Termine (oder Termine, die sich "an den Rändern" überschneiden, die aber nicht alle "in den ersten Termin hineinragen").

wenn nicht, muss ich mir mal doch eine beispieldatenbank machen und ein wenig "rumfummeln".

Hier wäre der entprechende CREATE und INSERTs. Ich habe mal auf DATETIME-Spalten umgestellt, das vereinfacht vielleicht später komplexere WHERE-Bedingungen.

  
CREATE TABLE `termine` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `begin` datetime NOT NULL default '0000-00-00 00:00:00',  
  `end` datetime NOT NULL default '0000-00-00 00:00:00',  
  PRIMARY KEY  (`id`)  
);  
  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 08:40:00', '2006-05-30 08:50:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 08:50:00', '2006-05-30 09:05:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 09:00:00', '2006-05-30 09:25:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 09:15:00', '2006-05-30 09:35:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 09:30:00', '2006-05-30 09:40:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 09:45:00', '2006-05-30 10:05:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 09:45:00', '2006-05-30 10:10:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 10:30:00', '2006-05-30 10:40:00');  
INSERT INTO `termine` (`begin`, `end`) VALUES ('2006-05-30 10:40:00', '2006-05-30 10:45:00');

Danke Dir für Deine Mühe,
David

[Ilja]

yo,

Das Problem ist, dass wenn man der Selektionsbedingung AND tab2.begin <= tab1.end hinzufügt, nur "direkte" Überlappungen berücksichtigt werden, aber nicht z.B. drei direkt hintereinanderliegende Termine (oder Termine, die sich "an den Rändern" überschneiden, die aber nicht alle "in den ersten Termin hineinragen").

ja, das ist ein problem, wir haben verkettungen von terminen. deswegen habe ich die strategie geändert, da du ja sowieso die zwischenräume haben willst.

macht schon sinn, solche doch etwas aufwänderingen abfragen selbst auszuprobieren, dann werden die probleme klarer, anstelle wie ich ständig herumzudoktern. leider musste ich erst mal den apache freund installieren, da meine alte mysql version keine unterabfragen kann und dann auch noch der alte apache bei bestimmten abfragen fehlermeldungen brachte. und dann auch noch das problem angehen, das dauert schon. ;-)

wir auch immer, ich habe (hoffentlich) eine lösung gefunden. anstelle die verkettung zu verfolgen, habe ich den datensatz gesucht, der keinen nachfolger hat. sein enddatum ausgegeben zusammen mit den nächsten beginn, dass sollte es sein.

beim letzten datensatz kommt natürlich ein NULL wert für den Start raus, da kann man einen anderen wert eintragen, wenn du willst. aber ich habe ihn erst mal so stehen lassen

SELECT tab1.id, tab1.end AS 'Start',
 (
  SELECT MIN(tab3.begin)
  FROM termine AS tab3
  WHERE tab3.begin > tab1.end
 ) AS 'Ende'
FROM termine AS tab1
WHERE
 (
  SELECT COUNT( * )
  FROM termine AS tab2
  WHERE tab1.end
  BETWEEN tab2.begin
  AND tab2.end
  AND tab1.id <> tab2.id
 ) = 0

btw. vielen dfank für die tabellen struktur, ich habe aber die spalte date vermisst ?

gruß
Ilja

[David Aurelio]

Hallo Ilja,

SELECT tab1.id, tab1.end AS 'Start',
(
  SELECT MIN(tab3.begin)
  FROM termine AS tab3
  WHERE tab3.begin > tab1.end
) AS 'Ende'
FROM termine AS tab1
WHERE
(
  SELECT COUNT( * )
  FROM termine AS tab2
  WHERE tab1.end
  BETWEEN tab2.begin
  AND tab2.end
  AND tab1.id <> tab2.id
) = 0

Das funtioniert wunderbar, hab vielen Dank!

btw. vielen dfank für die tabellen struktur, ich habe aber die spalte date vermisst ?

Ich habe die Spaltentypen von begin und end auf "DATETIME" gesetzt, daher ist die Spalte "date" nicht mehr erforderlich gewesen.

schönen Gruß,
David

[Kalle_B]

Denkansatz (nicht getestet):

ich greife mir jeden Termin:

SELECT ...
FROM   termin AS ter1

dann hole ich die überlappenden Termine dazu:

LEFT JOIN termin AS ter2
ON        ter2.date=ter1.date AND ter2.begin<ter1.end AND ter2.end>ter1.begin

Kalle

[David Aurelio]

Hallo Kalle,
ich habe ein bisschen mit den JOIN-Bedingungen herumgespielt, aber das will nicht so richtig.
Danke Dir trotzdem,
schönen Gruß,
David.