4. [SQL-Server] Indirekte Raumüberlappung bei Terminen...

[SQL-Server] Indirekte Raumüberlappung bei Terminen...

  • A

    Wir haben 3 Tabellen (hier stark vereinfacht und für das bessere Verständnis umbenannt):

    create table tblRessource (
     id int not null,
     idRessourcenart int not null,
     primary key (id)
);
create table tblTermin (
     id int not null,
     beginn datetime2 not null,
     ende datetime2 not null,
     primary key (id)
);
create table tblTerminRessoure (
     idTermin int not null,
     idRessource int not null,
     primary key (idTermin, idRessource)
);

    Nun überschneiden sich 3 parallele Termine in einem Teil der Räume:
    1. Termin: idRessource 1+3
    2. Termin: idRessource 2+3
    3. Termin: idRessource 2

    Für die Vereinfachung wird zudem angenommen das sich die Termine exakt, ohne zeitlichen Versatz, überschneiden.

    Selektiere ich aus Terminsicht die direkte Überschneidung wird mir ausgehend vom ersten oder dritten Termins nur der zweite zurückgeliefert. Ich komme derzeit nicht auf eine möglichst einfache Lösung (möglichst in Form eines Selekts) wie ich auch eine indirekte überlappung selektieren kann. Sprich: nehmen wir den ersten Termin als Ausgangspunkt, dieser hat eine direkte Überschneidung mit dem 2ten, nicht aber den dritten Termin. Nimmt man aber die Ressourcen des zweiten hinzu (indirekte Überschneidung über Ressource 2) bekäme ich alle 3 Termine.

    Hat jemand eine Idee, wie ich solche "indirekten" Überschneidungen möglichst innerhalb eines Selects ermitteln kann? Mir fällt derzeit keine einfache Lösung ein.

    0

  • Rekursive Common Table Expressions?

    0
  • A

    hustbaer schrieb:

    Rekursive Common Table Expressions?

    Musste jetzt erst den Ausdruck googeln, um dann festzustellen das ich so etwas an anderer Stelle bereits verwende... Vielleicht übersehe ich dabei etwas, aber die Gefahr könnte hier eine Rekursionsschleife sein. Müsste noch prüfen wie ich dies bei RCTE umgehen kann.

    0

  • Die Rekursion läuft nur doch AFAIK nur so lange weitere Datensätze dazukommen. Muss also ganz von selbst irgendwann terminieren.

    0
  • A

    hustbaer schrieb:

    Die Rekursion läuft nur doch AFAIK nur so lange weitere Datensätze dazukommen. Muss also ganz von selbst irgendwann terminieren.

    Nachdem ich zwischenzeitlich an etwas anderen saß und nun wieder auf das Thema zurückkomme... Nein, die unendliche Rekursion scheint sich so einfach nicht vermeiden zu lassen. Um sie zu begrenzen muss man mit Stringoperatoren (Wertepaare in den Rekursionen ergänzen und prüfen das dieses Wertepaar nicht schon vorkam).

    Siehe auch:
    http://weblogs.sqlteam.com/peterl/archive/2008/11/27/Expand-network-using-CTE-without-circular-reference.aspx

    0
  • T

    könntest du die inalte der 3 tabellen posten (nur die betroffenen datensätze) ?
    und du willst alle möglichen überschneidungen ermitteln, ja?
    den begriff "indirekt" habe ich so nicht ganz verstanden.

    und das feld "idRessourcenart" soll den raum darstellen, oder?

    tenim

    0
  • A

    tenim schrieb:

    könntest du die inalte der 3 tabellen posten (nur die betroffenen datensätze) ?
    und du willst alle möglichen überschneidungen ermitteln, ja?
    den begriff "indirekt" habe ich so nicht ganz verstanden.

    und das feld "idRessourcenart" soll den raum darstellen, oder?

    1. Unten habe ich ein vollständiges Beispiel erzeugt (Tabellen+Daten)
    2. Ich will alle möglichen Raumüberschneidungen paralleler Termine ermitteln, immer ausgehend von einem Termin. Mit indirekt meine ich das z.B. der erste Termin keine direkte Ressourcenüberschneidung mit den dritten hat, aber beide über den zweiten eine überschneidung haben. Sprich: Egal von welchen der 3 Termine ich ausgehe, müssen mir alle 3 Räume geliefert werden (Idealerweise eine Liste die IdTermin + IdRaum liefert; Sortierung ist vollkommen egal).
    3. Die IdRessourcenart steht für die Art (Raum, Person...) einer Ressource, hier nehme ich einfach mal 200 für Räume an.

    Derzeit löse ich es aufwendiger und in Teilabschnitten ohne CTE, mich würde aber eine direkte Lösung interessieren (da ich immer mal ähnliche Fälle habe). Für CTE habe ich halt nur eine sehr aufwändige Lösung gefunden um eine rekursion zu verhindern.

    create table tblRessource (
     id int not null,
     idRessourcenart int not null,
     primary key (id)
);

insert into tblRessource (id, idRessourcenart ) values (1, 200);
insert into tblRessource (id, idRessourcenart ) values (2, 200);
insert into tblRessource (id, idRessourcenart ) values (3, 200);

create table tblTermin (
     id int not null,
     beginn datetime2 not null,
     ende datetime2 not null,
     primary key (id)
);

insert into tblTermin (id, beginn, ende) values (1, '2018-03-06 12:30:00', '2018-03-06 14:00:00');
insert into tblTermin (id, beginn, ende) values (2, '2018-03-06 12:30:00', '2018-03-06 14:00:00');
insert into tblTermin (id, beginn, ende) values (3, '2018-03-06 12:30:00', '2018-03-06 14:00:00');

create table tblTerminRessoure (
     idTermin int not null,
     idRessource int not null,
     primary key (idTermin, idRessource)
);

insert into tblTerminRessoure (idTermin, idRessource) values (1, 1);
insert into tblTerminRessoure (idTermin, idRessource) values (1, 3);
insert into tblTerminRessoure (idTermin, idRessource) values (2, 2);
insert into tblTerminRessoure (idTermin, idRessource) values (2, 3);
insert into tblTerminRessoure (idTermin, idRessource) values (3, 2);
    0
  • T

    select idTermin, idRessource
    from tblTerminRessoure
    where idRessource IN(select idRessource from tblTerminRessoure group by idRessource having count(idRessource)>1)
    order by idRessource, idTermin

    die annahme dahinter (dein beispiel, nach dem preselect nach sich überlappenden terminen):

    * in der tblTerminRessoure gibt es nur termine, die sich überlappen.
    * zu jedem termin gibt nur distinkte ressourcen
    * wenn eine ressource mehr als einmal vorkommt, muss sich diese überlappen

    p.s.

    ressource 1 kommt nicht vor, da diese nur von einem termin genutzt wird.

    0
  • A

    tenim schrieb:

    die annahme dahinter

    ...ist komplett falsch.

    Ich habe ein Minimalbeispiel erstellt (was in diesem Forum eigentlich auch so verlangt wird), um das Problem zu verdeutlichen. Ein Realfall mit vielen 1000 Terminen ergibt keinen Sinn. Auch habe ich nur Räume als Ressourcen verwendet.

    Aber um einen etwas realistischeren Fall darzustellen, ein Beispiel mit weiteren Daten:

    create table tblRessource (
     id int not null,
     idRessourcenart int not null,
     primary key (id)
);

insert into tblRessource (id, idRessourcenart ) values (1, 200);
insert into tblRessource (id, idRessourcenart ) values (2, 200);
insert into tblRessource (id, idRessourcenart ) values (3, 200);
insert into tblRessource (id, idRessourcenart ) values (4, 200);
insert into tblRessource (id, idRessourcenart ) values (5, 200);
insert into tblRessource (id, idRessourcenart ) values (6, 200);
insert into tblRessource (id, idRessourcenart ) values (7, 200);

create table tblTermin (
     id int not null,
     beginn datetime2 not null,
     ende datetime2 not null,
     primary key (id)
);

insert into tblTermin (id, beginn, ende) values (1, '2018-03-06 12:30:00', '2018-03-06 14:00:00');
insert into tblTermin (id, beginn, ende) values (2, '2018-03-06 12:30:00', '2018-03-06 14:00:00');
insert into tblTermin (id, beginn, ende) values (3, '2018-03-06 12:30:00', '2018-03-06 14:00:00');
insert into tblTermin (id, beginn, ende) values (4, '2018-03-06 10:00:00', '2018-03-06 12:30:00');
insert into tblTermin (id, beginn, ende) values (5, '2018-03-06 11:00:00', '2018-03-06 13:30:00');
insert into tblTermin (id, beginn, ende) values (6, '2018-03-06 11:00:00', '2018-03-06 13:30:00');
insert into tblTermin (id, beginn, ende) values (7, '2018-03-06 12:00:00', '2018-03-06 15:00:00');
insert into tblTermin (id, beginn, ende) values (8, '2018-03-06 13:00:00', '2018-03-06 15:00:00');
insert into tblTermin (id, beginn, ende) values (9, '2018-03-06 14:00:00', '2018-03-06 15:30:00');

create table tblTerminRessoure (
     idTermin int not null,
     idRessource int not null,
     primary key (idTermin, idRessource)
);

insert into tblTerminRessoure (idTermin, idRessource) values (1, 1);
insert into tblTerminRessoure (idTermin, idRessource) values (1, 3);
insert into tblTerminRessoure (idTermin, idRessource) values (2, 2);
insert into tblTerminRessoure (idTermin, idRessource) values (2, 3);
insert into tblTerminRessoure (idTermin, idRessource) values (3, 2);
insert into tblTerminRessoure (idTermin, idRessource) values (4, 1);
insert into tblTerminRessoure (idTermin, idRessource) values (4, 2);
insert into tblTerminRessoure (idTermin, idRessource) values (5, 4);
insert into tblTerminRessoure (idTermin, idRessource) values (5, 5);
insert into tblTerminRessoure (idTermin, idRessource) values (6, 4);
insert into tblTerminRessoure (idTermin, idRessource) values (7, 6);
insert into tblTerminRessoure (idTermin, idRessource) values (8, 7);
insert into tblTerminRessoure (idTermin, idRessource) values (9, 1);

    tenim schrieb:

    * in der tblTerminRessoure gibt es nur termine, die sich überlappen.

    Ist dem Minimalbespiel geschuldet

    tenim schrieb:

    * wenn eine ressource mehr als einmal vorkommt, muss sich diese überlappen

    Stimmt bereits mit meiner Beschreibung einige Posts vorher NICHT überein. Termin 3 hat nur eine Ressource, weißt aber dennoch eine überlappung auf. Und auch wenn ein Zeitfenster mehreren Ressourcen zugeordnet ist MUSS KEINE Überlappung existieren.

    tenim schrieb:

    ressource 1 kommt nicht vor, da diese nur von einem termin genutzt wird.

    Doch, sie kommt vor und ist auch wichtig. Ich brauche einen Select der Erkennt das Zeitfenster 1-3 miteinander überlappen (und sei es nur durch einen Teil untereinander) UND alle beteiligten Räume.

    0
  • T

    ok, ich verstehe jetzt was du meinst.

    0
  • T

    Stimmt bereits mit meiner Beschreibung einige Posts vorher NICHT überein. Termin 3 hat nur eine Ressource, weißt aber dennoch eine überlappung auf. Und auch wenn ein Zeitfenster mehreren Ressourcen zugeordnet ist MUSS KEINE Überlappung existieren.

    ja, weil diese ressouce noch in termin 2 vorkommt.
    das hatte ich ja gesagt: wenn eine ressource bei mehreren terminen (anz. termine>1), die sich zeitlich überlappen mehr als einmal vorkommt (ressourcentyp>1), dann muss sich diese ressource überlappen, da sie zur selben zeit von n>1 verschiedenen terminen verwendet werden will.

    Doch, sie kommt vor und ist auch wichtig. Ich brauche einen Select der Erkennt das Zeitfenster 1-3 miteinander überlappen (und sei es nur durch einen Teil untereinander) UND alle beteiligten Räume.

    wenn du bei sich überschneidenen terminen generell ALLE beteidigten räume willst, warum dann dieses prüfen der raumüberlappungen?

    0
Anmelden zum Antworten