SQL - Overlappende reserveringen op dezelfde kamer

[aegieles]

Graag jullie hulp bij een query:
Ik probeer uit een bestand met reserveringen de overlappende reserveringen op dezelfde kamer te filteren, maar ik kom er niet uit.

Ik heb de de volgende query geprobeerd:

SQL:

SELECT a.*
FROM Reserveringen AS a 
INNER JOIN Reserveringen AS b 
ON (a.Nummer = b.Nummer) 
  AND (a.Kamer = b.Kamer) 
  AND (a.Startdatum_tijd <= b.Einddatum_tijd)
  AND ( a.Einddatum_tijd >= b.Startdatum_tijd);

Het resultaat is niet wat ik probeer te bereiken. Wie kan mij helpen?

[ Voor 2% gewijzigd door MueR op 07-08-2020 20:42 . Reden: Code block toegevoegd ]

[emnich]

Zonder context is het wat lastig maar als ik er van uit ga dat Nummer je reserveringsnummer is, dan wil je die juist niet gelijk hebben dus ik zou beginnen met die uit je JOIN te halen

[Polderviking]

Ik denk dat je toch even op de proppen moet komen met hoe dat tabel er uit ziet en welke data waar staat.

Hier in je join doe je bijvoorbeeld a.Nummer = b.Nummer.
Als dat een uniek reserveringsnummer is ga je nooit je dubbele reservering vinden?

Kan je in dat reserveringstabel niet gewoon goupen op kamernummer?

[ Voor 25% gewijzigd door Polderviking op 07-08-2020 18:12 ]

[Juup]

aegieles schreef op vrijdag 7 augustus 2020 @ 18:01:
Het resultaat is niet wat ik probeer te bereiken.

Kan het nog vager?
Wat krijg je?
Wat zou je willen krijgen?
Laat je output zien.
Hoe zien je tabellen er uit?

[Onbekend]

Je hebt in jouw query nu staan dat reservering A volledig over B moet overlappen. Dus Ma t/m Vr voor A, en dan moet B Di t/m Do zijn. Als B Wo t/m Za heeft werkt het niet. Je moet ook half overlappend bekijken.

De query ziet er ongeveer als volgt uit:
SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen AS a
INNER JOIN Reserveringen AS b
ON (a.Kamer = b.Kamer) AND
((a.Startdatum_tijd >= b.Startdatum_tijd AND a.Startdatum_tijd <= b.Einddatum_tijd) OR
(a.Einddatum_tijd >= b.Startdatum_tijd AND a.Einddatum_tijd <= b.Einddatum_tijd))

[aegieles]

Reserveringen: https://pastebin.com/43QiK0Bj
Resultaat: https://pastebin.com/rAQaZt5s

Ik krijg net zoveel records als resultaat als erin de tabel staat, dat kan nooit. De query lijkt niks met de vergelijking van de kamer te doen.

[ Voor 55% gewijzigd door aegieles op 07-08-2020 18:19 ]

[aegieles]

Reserveringen: https://pastebin.com/43QiK0Bj
Resultaat: https://pastebin.com/rAQaZt5s

Ik krijg net zoveel records als resultaat als erin de tabel staat, dat kan nooit. De query lijkt niks met de vergelijking van de kamer te doen.

[aegieles]

Onbekend schreef op vrijdag 7 augustus 2020 @ 18:11:
Je hebt in jouw query nu staan dat reservering A volledig over B moet overlappen. Dus Ma t/m Vr voor A, en dan moet B Di t/m Do zijn. Als B Wo t/m Za heeft werkt het niet. Je moet ook half overlappend bekijken.

De query ziet er ongeveer als volgt uit:
SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen AS a
INNER JOIN Reserveringen AS b
ON (a.Kamer = b.Kamer) AND
((a.Startdatum_tijd >= b.Startdatum_tijd AND a.Startdatum_tijd <= b.Einddatum_tijd) OR
(a.Einddatum_tijd >= b.Startdatum_tijd AND a.Einddatum_tijd <= b.Einddatum_tijd))

Dat resulteerd in output met 2 keer zoveel records als in de tabel met reserveringen.

[Luit]

code:

SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen a
INNER JOIN Reserveringen b ON
a.Kamer = b.Kamer
AND (a.Startdatum_tijd BETWEEN b.Startdatum_tijd AND b.Einddatum_tijd
  OR b.Startdatum_tijd BETWEEN a.Startdatum_tijd AND a.Einddatum_tijd)

Je checkt hiermee of reserveringen in a (deels) overlappen met reserveringen in b en visa versa. Dit is de query voor MS T-SQL. Indien nodig kan je de BETWEEN herschrijven als:

code:

SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen AS a
INNER JOIN Reserveringen AS b ON
a.Kamer = b.Kamer
AND ((a.Startdatum_tijd >= b.Startdatum_tijd AND a.Startdatum_tijd <= b.Einddatum_tijd)
  OR (b.Startdatum_tijd >= a.Startdatum_tijd AND b.Startdatum_tijd <= a.Einddatum_tijd))

[ Voor 34% gewijzigd door Luit op 07-08-2020 18:46 ]

[gekkie]

Indien PostgreSQL timestamp range en de overlap (&&) operator gebruiken ?

[Onbekend]

aegieles schreef op vrijdag 7 augustus 2020 @ 18:24:
[...]


Dat resulteert in output met 2 keer zoveel records als in de tabel met reserveringen.

Maar zijn de resultaten zelf wel goed?
En de query was meer een hint. Als het goed is kan je zelf de query verder wel afmaken.

[aegieles]

Onbekend schreef op vrijdag 7 augustus 2020 @ 18:35:
[...]

Maar zijn de resultaten zelf wel goed?
En de query was meer een hint. Als het goed is kan je zelf de query verder wel afmaken.

Het bestand met reserveringen zijn er iets meer als duizend. Het aantal reserveringen met overlappende data op dezelfde kamer (dubbele kamer boeking) zouden er 10-tallen moeten zijn. Het voorbeeld wat jij mij gaf resulteert in een output met meer dan 2000 records. Nee het lukt me niet om de query goed in elkaar te krijgen. Op een of andere manier heb ik het idee dat de query geen rekening houd dat de kamer hetzelfde moet zijn.

Om reserveringen te krijgen met overlappende datums is niet het probleem, daar is genoeg info op internet over te vinden, zoals hier. Ik loop alleen aan tegen het probleem dat dit allen moet gelden voor reserveringen op dezelfde kamer. Deze combo van voorwaarden kan ik helemaal niets over vinden op internet, na een hoop gegoogle.

[ Voor 23% gewijzigd door aegieles op 07-08-2020 19:04 ]

[BertS]

Toch zouden de aangereikte queries dat wel moeten doen. Neem je ze wel goed over, en dan vooral met de haakjes?

Sowieso wel handig trouwens als je vermeld welk RDBMS je gebruikt.

[Onbekend]

aegieles schreef op vrijdag 7 augustus 2020 @ 18:53:
Het bestand met reserveringen zijn er iets meer als duizend. Het aantal reserveringen met overlappende data op dezelfde kamer (dubbele kamer boeking) zouden er 10-tallen moeten zijn. Het voorbeeld wat jij mij gaf resulteert in een output met meer dan 2000 records. Nee het lukt me niet om de query goed in elkaar te krijgen. Op een of andere manier heb ik het idee dat de query geen rekening houd dat de kamer hetzelfde moet zijn.

Hij houdt daar wel rekening mee.
Maar als je 2 boekingen hebt die elkaar overlappen, krijg je 2 resultaten.
Heb je er 3, dan krijg je 6 resultaten. Heb je 4 overlappende, dan krijg je 12 resultaten.
Anders gebruik je DISTINCT als je dubbele data terug krijgt.

(De datumvelden zijn ook echt datums of numerieke velden? Want het lijkt alsof het boekingsnummer gewoon een tekstveld is?)

[itons]

File even omgebutst naar insert statements: https://sqlizer.io/#/resu...KvVddsk8iHo_6LFg5gqq5jg==

Deze query laat de dubbele boekingen zien

code:

select
 *
from
 public."43QiK0Bj" a,
 public."43QiK0Bj" b
where
 a."Kamer" = b."Kamer"
 AND
 (
     (a."Startdatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
     OR
     (a."Einddatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
 )
 AND
 a."Nummer" < b."Nummer"
 ORDER BY a."Nummer"
 ;

Deze query het aantal dubbele boekingen per kamer:

code:

select
 distinct a."Kamer", count(a."Kamer")
from
 public."43QiK0Bj" a,
 public."43QiK0Bj" b
where
 a."Kamer" = b."Kamer"
 AND
 (
     (a."Startdatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
     OR
     (a."Einddatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
 )
 AND
 a."Nummer" < b."Nummer"
 GROUP BY a."Kamer"
 ;

[aegieles]

BertS schreef op vrijdag 7 augustus 2020 @ 19:06:
Toch zouden de aangereikte queries dat wel moeten doen. Neem je ze wel goed over, en dan vooral met de haakjes?

Sowieso wel handig trouwens als je vermeld welk RDBMS je gebruikt.

Ja ik neem ze netjes over. Ik gebruik Access b.t.w. Als dan de queries goed zouden moeten zijn. Zou ik iets verkeerd moeten hebben gedaan met de data dump die ik heb gemaakt, waardoor ik hele andere resultaten krijg dan ik verwacht. Ik ga het morgen bekijken en een kleinere dump maken, zodat ik handmatig de output kan checken.

[aegieles]

Onbekend schreef op vrijdag 7 augustus 2020 @ 19:13:
[...]

Hij houdt daar wel rekening mee.
Maar als je 2 boekingen hebt die elkaar overlappen, krijg je 2 resultaten.
Heb je er 3, dan krijg je 6 resultaten. Heb je 4 overlappende, dan krijg je 12 resultaten.
Anders gebruik je DISTINCT als je dubbele data terug krijgt.

(De datumvelden zijn ook echt datums of numerieke velden? Want het lijkt alsof het boekingsnummer gewoon een tekstveld is?)

Oh met DISTINCT was ik nog niet mee bekend. Ik ga morgen verder puzzelen. Thx voor zover.

[aegieles]

itons schreef op vrijdag 7 augustus 2020 @ 20:20:
File even omgebutst naar insert statements: https://sqlizer.io/#/resu...KvVddsk8iHo_6LFg5gqq5jg==

Deze query laat de dubbele boekingen zien

code:

select
 *
from
 public."43QiK0Bj" a,
 public."43QiK0Bj" b
where
 a."Kamer" = b."Kamer"
 AND
 (
     (a."Startdatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
     OR
     (a."Einddatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
 )
 AND
 a."Nummer" < b."Nummer"
 ORDER BY a."Nummer"
 ;

Deze query het aantal dubbele boekingen per kamer:

code:

select
 distinct a."Kamer", count(a."Kamer")
from
 public."43QiK0Bj" a,
 public."43QiK0Bj" b
where
 a."Kamer" = b."Kamer"
 AND
 (
     (a."Startdatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
     OR
     (a."Einddatum_tijd" between b."Startdatum_tijd" and b."Einddatum_tijd")
 )
 AND
 a."Nummer" < b."Nummer"
 GROUP BY a."Kamer"
 ;

Oke ga het morgen uitproberen. Ben er nu wel even klaar mee . Een group by gebruiken...mmm, die tip heb ik nog niet gehad. Thx voor zover.

[edeboeck]

aegieles schreef op vrijdag 7 augustus 2020 @ 20:26:
[...]


Oke ga het morgen uitproberen. Ben er nu wel even klaar mee . Een group by gebruiken...mmm, die tip heb ik nog niet gehad. Thx voor zover.

De GROUP BY is hier enkel nodig om de COUNT te kunnen doen.

[scatman]

aegieles schreef op vrijdag 7 augustus 2020 @ 18:01:
SELECT a.*
FROM Reserveringen AS a INNER JOIN Reserveringen AS b ON (a.Nummer = b.Nummer) AND (a.Kamer = b.Kamer) AND (a.Startdatum_tijd <= b.Einddatum_tijd) AND ( a.Einddatum_tijd >= b.Startdatum_tijd);

Eén ding nog vergeten: "and a.id !=b.id"

[aegieles]

scatman schreef op vrijdag 7 augustus 2020 @ 20:36:
[...]

Eén ding nog vergeten: "and a.id !=b.id"

Oh oke dan wordt het: a.Nummer != b.Nummer en heb ik de DESTICT niet nodig.

En ja Nummer is in mijn db een tekstveld, de startdatum en einddatum zijn daadwerkelijk datum/tijd velden.

[aegieles]

edeboeck schreef op vrijdag 7 augustus 2020 @ 20:31:
[...]

De GROUP BY is hier enkel nodig om de COUNT te kunnen doen.

oh oke is dan niet wat ik wil

[Voutloos]

scatman schreef op vrijdag 7 augustus 2020 @ 20:36:
[...]

Eén ding nog vergeten: "and a.id !=b.id"

Dit is de enige juiste aanvulling t.o.v. je startpost. Hoe tegenintuitief het ook lijkt voor diverse meedenkende tweakers: a.start <= b.eind AND a.eind >= b.start is volledig juist als je gedeeltelijke of gehele overlap zoekt.

Of je de periode als exclusief of inclusief ziet, dus of 1 exacte seconde als overlap telt is een kwestie van kiezen tussen '<' en '<=', maar verder hoef je aan die periode checks niet te sleutelen.

[ Voor 20% gewijzigd door Voutloos op 07-08-2020 21:01 ]

[aegieles]

Voutloos schreef op vrijdag 7 augustus 2020 @ 20:50:
[...]

Dit is de enige juiste aanvulling t.o.v. je startpost. Hoe tegenintuitief het ook lijkt voor diverse meedenkende tweakers: a.start <= b.eind AND a.eind >= b.start is volledig juist als je gedeeltelijke of gehele overlap zoekt.

Of je de periode als exclusief of inclusief ziet, dus of 1 exacte seconde als overlap telt is een kwestie van kiezen tussen '<' en '<=', maar verder hoe je aan die periode checks niet te sleutelen.

Helder, ik had dit ook met veel theorie op andere fora's gezien m.b.t. overlappende datums.
Excuses "emnich", "scatman" en "voutloos dat ik een beetje aan de eerste post voorbij ben gegaan, maar is logisch als ik er nu wat beter over denk", overlappende reserveringen met hetzelfde nummer wil ik niet in mijn overzicht te hebben.

[ Voor 6% gewijzigd door aegieles op 07-08-2020 22:22 ]

[The Eagle]

Luit schreef op vrijdag 7 augustus 2020 @ 18:30:

code:

SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen a
INNER JOIN Reserveringen b ON
a.Kamer = b.Kamer
AND (a.Startdatum_tijd BETWEEN b.Startdatum_tijd AND b.Einddatum_tijd
  OR b.Startdatum_tijd BETWEEN a.Startdatum_tijd AND a.Einddatum_tijd)

Je checkt hiermee of reserveringen in a (deels) overlappen met reserveringen in b en visa versa. Dit is de query voor MS T-SQL. Indien nodig kan je de BETWEEN herschrijven als:

code:

SELECT a.Kamer, a.Nummer, b.Nummer
FROM Reserveringen AS a
INNER JOIN Reserveringen AS b ON
a.Kamer = b.Kamer
AND ((a.Startdatum_tijd >= b.Startdatum_tijd AND a.Startdatum_tijd <= b.Einddatum_tijd)
  OR (b.Startdatum_tijd >= a.Startdatum_tijd AND b.Startdatum_tijd <= a.Einddatum_tijd))

In dit geval kun je idd between gebruiken, omdat die bij Tsql inclusive is, ie hij oakt de grenzen mee. Zie https://www.w3schools.com/sql/sql_between.asp Dat is niet bij iedere DB het geval; Oracle doet dit bijvoorbeeld anders. Ik weet even niet hoe Access er mee om gaat.

[aegieles]

The Eagle schreef op vrijdag 7 augustus 2020 @ 21:05:
[...]

In dit geval kun je idd between gebruiken, omdat die bij Tsql inclusive is, ie hij oakt de grenzen mee. Zie https://www.w3schools.com/sql/sql_between.asp Dat is niet bij iedere DB het geval; Oracle doet dit bijvoorbeeld anders. Ik weet even niet hoe Access er mee om gaat.

Of Access dit slikt, weet ik ook niet. Er zijn soms meerdere wegen naar Rome qua syntax. Het probleem met overlappende datums ben ik nu wel uit volgens mij. Waarschijnlijk ben ik op een dwaalspoor beland door een slechte dump te maken en door het Nummer gelijk te stellen in de query, waardoor ik andere resultaten kreeg dan ik verwachte. Volgens mij zit de oplossing in de in de post van "voutloos" en de eerste poster. Laat het morgen denk ik weten als ik er uit kom wat de oplossing zou moeten zijn. Toch een leuke case om te werken met overlappende datums. Thx

[aegieles]

volgt....

[ Voor 97% gewijzigd door aegieles op 07-08-2020 22:28 ]

[aegieles]

Ik ben eruit. Ik krijg nu de resultaten die ik verwacht. Onderstaande is de juiste query:

code:

SELECT a.*
FROM Reserveringen AS a INNER JOIN Reserveringen AS b ON (a.Kamer = b.Kamer) 
AND (a.Nummer <> b.Nummer) 
AND ( a.Einddatum_tijd >= b.Startdatum_tijd) AND (a.Startdatum_tijd <= b.Einddatum_tijd) ;

Bedankt voor jullie hulp!

[ Voor 3% gewijzigd door aegieles op 08-08-2020 16:47 ]

[The Eagle]

En voor de bonuspunten: hoe toon je aan dat dat idd alle dubbelingen zijn en dat je er geen vergeten bent?

[Voutloos]

Haha, Eagle,

spoiler:

die bonuspunten haal je al met de startpost, want als je rows met zichzelf vergelijkt heb je inderdaad altijd overlap en dus de gehele dataset.

[The Eagle]

@Voutloos

spoiler:

Ik doelde eerder op een count van de niet overlappende rijen en een count van de wel overlappende rijen vs totaal aantal rijen. Iets met een Venn diagram in je hoofd hebben en goed gebruik van de union, minus en count statements. Een select * van de hele tabel minus een select * van niet overlappend zou exact het zelfde resultaat op moeten leveren als de wel overlappende rijen. Dat wil nog wel eens net niet het geval zijn

[Voutloos]

@The Eagle Ok, ik twijfelde of je dat bedoelde, maar zie daar hier de waarde niet van eerlijk gezegd. Als je de conditie omdraait heb je de rest vd rows. Als dat niet klopt hier, heb je het eerder brak omgedraaid dan dat de overlap check niet klopt. Nogmaals, dit is de minimale, doch volledige check. Als je die door hebt, is de query niet zo spannend.

Wel een detail: Bij meerdere simultane overlaps krijg je rows uit a wel dubbel terug. Dat kan je misschien zo spotten, maar dat lijkt mij redelijk goed te beredeneren als je enkel naar de join kijkt.

Product dat ik ontwikkel heeft meerdere domeinen waarin planning voor komt, dus overlap check gaat hier op de autopilot . Voor de between-achtige queries, bij jullie kom ik tijdens je langere vakantie halverwege een nachtje je hotelkamer eisen, blijkbaar mag dat.

[ Voor 19% gewijzigd door Voutloos op 08-08-2020 21:30 ]