Date range test
De feiten:
- Ik heb 2 kamers beschikbaar.
- Een kamer kan voor één of meerdere dagen gereserveerd worden.
- Een date range mag op dezelfde dag beginnen als een andere date range eindigt
De query:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
<?php
$query = "
SELECT *
FROM reserveringen
WHERE `reservering_eind` > '" . $datum1 . "'
AND `reservering_start` < '" . $datum2 . "'
";
?>
$query = "
SELECT *
FROM reserveringen
WHERE `reservering_eind` > '" . $datum1 . "'
AND `reservering_start` < '" . $datum2 . "'
";
?>
Met behulp van bovenstaande query kan ik bepalen hoeveel reserveringen er zijn in de gekozen date range van $datum1 tot $datum2. Als er 2 resultaten zijn, betekent dit dat er geen kamers beschikbaar zijn.
Echter als de date range die ik wil testen bijvoorbeeld een week of een maand beslaat. Dan kunnen er zelfs meer dan 2 reserveringen gevonden worden. Ook als alle gevonden reserveringen na elkaar op kamer 1 geboekt kunnen worden.
Hoe kan ik nu testen of de gevonden reserveringen elkaar overlappen. Want als ze dat niet doen zou de kamer wel nog beschikbaar zijn.
Voorbeeld:
reservering #1: 01 jan 2012 tot 02 jan 2012
reservering #2: 02 jan 2012 tot 03 jan 2012
reservering #3: 03 jan 2012 tot 04 jan 2012
Test input:
reservering #4: 01 jan 2012 tot 04 jan 2012
Bovenstaande (foute) query geeft terug dat er reeds 3 reserveringen zijn in periode 01 tot 04 jan 2012. De query zou terug moeten geven dat er van de 2 beschikbare kamers er slechts max 1 in gebruik is tijdens de gevraagde periode.
Iemand enig idee hoe ik dit kan doen?
EDIT:
Vincent opperde het volgende:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
$query = "
SELECT COUNT(*), kamer_id
FROM reserveringen
WHERE reservering_eind > $datum1
AND reservering_start < $datum2
GROUP BY kamer_id
HAVING COUNT(*)=0
";
?>
$query = "
SELECT COUNT(*), kamer_id
FROM reserveringen
WHERE reservering_eind > $datum1
AND reservering_start < $datum2
GROUP BY kamer_id
HAVING COUNT(*)=0
";
?>
Echter de reserveringen hebben niet altijd een kamer_id. Als een reservering toegevoegd word, wordt er niet direct een kamer_id gekoppeld. Dit kan later gedaan worden.
Iemand een idee over hoe dit op te lossen?
dan selecteer je het aantal reserveringen, en dat aantal vergelijk je met het aantal kamers dat je hebt. dan heb je bijvoorbeeld 45 reserveringen, 50 kamers, kan er voor die periode nog 5x een reservering geplaatst worden. echter zou ik die query wat ingewikkelder maken..want als je al je kamers geboekt zijn van 10 december tot 27 december..gaat reserveren van bijvoorbeeld 8 december tot 15 december natuurlijk ook niet... alleen 8 en 9 december zijn dan nog kamers vrij ;)
Gewijzigd op 18/12/2012 12:29:28 door No One
Maar dan heb je nog niet het probleem opgelost van welke kamer. Zolang niet alle reserveringen aan kamers zijn toegewezen zal je dat vrees ik ook niet 1-2-3 lukken.
Toevoeging op 18/12/2012 12:31:55:
John D op 18/12/2012 12:30:14:
en >= $datum alsmede <= $datum gebruiken.
Nee. Een reservering voor 1 nacht loopt van 1 januari tot 2 januari. Maar op 2 januari is die kamer wel weer beschikbaar.
Erwin H op 18/12/2012 12:50:10:
Maar welke kamer? Stel je wilt een kamer een maand lang. Het feit dat er elke dag nog 1 kamer vrij is, wil niet zeggen de zelfde kamer elke dag. Zolang niet elke reservering aan een kamer is toegewezen kan je dus niet zeggen dat die reservering mogelijk.
zodra je reserveert weet het systeem: maakt niet uit welke kamer deze 4 zijn die hele periode nog vrij... bij een volgende reservering bij een soort gelijke periode ziet ie: oh, nog 3 vrij...enzv...in die periode heb je dan 4 kamers verdeeld over 4 personen die elke een ander bereik hebben binnen die periode..maar dat maakt niet uit...er is altijd een kamer voor wie dan ook. met drie kamers in de periode van 1 december tot 10 december: jij komt van 4 tot 7, ik kom van 1 tot 6 en iemand anders komt de gehele periode. dan is het duidelijk dat er sowieso 1 kamer vrij is van 1 tot 4 en van 6 tot 10 als die kamers die dagen geboekt zijn kan je op het scherm waarin je als nog een kamer toekent tonen welke kamers dus vrij zijn op de gereserveerde datums..
Gewijzigd op 18/12/2012 13:03:48 door No One
Ik heb kamer type A, B en C
Er zijn 2 type A kamers (kamernummer 1A en 2A), 4 type B en 6 type C kamers.
Nu is er een reservering van 1 dec tot 2 dec in een type A kamer (de reservering is toegewezen aan kamernummer 1A)
en er is een reservering van 2 dec tot 3 dec (deze reservering is nog niet toegewezen aan een kamer).
Nu wil ik een reservering gaan doen van 1 dec tot 3 dec.
Met mijn eerste query krijg ik alle reserveringen terug in de periode 1 dec tot 3 dec. Dit zijn er 2. Dit is gelijk aan het aantal beschikbare kamers dus wordt mijn reservering geweigerd. MAAR dit is natuurlijk niet zo. Want ik kan de tweede reservering toewijzen aan kamernummer 1A. Kamernummer 2A is dan nog gewoon beschikbaar in deze periode.
Ik ben inderdaad bang dat ik zoals Erwin al zei (Erwin H 18/12/2012 12:30:27) dat ik per dag van de datum range die ik wil controleren zal moeten gaan kijken of er nog kamers beschikbaar zijn op die dag. Of heeft er iemand nog een betere oplossing waarmee dit met één query zou kunnen?
Neem het volgende voorbeeld voor een hotel met 2 kamers:
R1)1 jan - 3 jan kamer 1
R2)4 jan - 6 jan kamer 1
R3)2 jan - 4 jan kamer 2
R4)5 jan - 7 jan kamer 2
Nu zie je dat als ik van 3 januari tot 5 januari wil boeken, ik de eerste nacht in kamer 1 zou kunnen zitten en de tweede nacht in kamer 2. Zolang er verder geen reserveringen zijn zou je kamers kunnen omgooien, maar zelfs dat zou onacceptabel kunnen zijn als mensen specifiek een kamer willen hebben.
Toevoeging op 18/12/2012 13:14:45:
Lars Anderson op 18/12/2012 13:10:13:
Ik ben inderdaad bang dat ik zoals Erwin al zei (Erwin H 18/12/2012 12:30:27) dat ik per dag van de datum range die ik wil controleren zal moeten gaan kijken of er nog kamers beschikbaar zijn op die dag. Of heeft er iemand nog een betere oplossing waarmee dit met één query zou kunnen?
Dit kan al met 1 query.
Gewijzigd op 18/12/2012 13:13:50 door Erwin H
in jou hotel zijn de reserveringen dus al gekoppeld aan kamernummers. maar als jij die kamernummers niet weet, dan weet je nog wel dat je de eerste nacht maar 1 kamer vrij hebt welke dat is maakt niet uit. als iemand langer wil zul je dus wie het eerst maalt systeem moeten gebruiken: de persoon die eerder gereserveerd heeft krijgt een kamer aaneengesloten, de persoon die later is kan je aangeven dat hij dan moet verkassen van kamer dat probleem los je echt niet op door de kamernummers wel te weten...tenslotte weet je zelfs met kamernummers in jou voorbeeld al dat iemand moet verkassen ;)
Toevoeging op 18/12/2012 13:25:14:
Je zou trouwens ook gewoon de eerstvolgende vrije kamer invullen en die later wijzigen als je dat dan toch zo perse nodig vind...dan weet je altijd een kamer...iemand die niet specifiek een kamer reserveerd maakt het toch niet uit.
Erwin H op 18/12/2012 13:13:20:
Dit kan al met 1 query.
Lars Anderson op 18/12/2012 13:10:13:
Ik ben inderdaad bang dat ik zoals Erwin al zei (Erwin H 18/12/2012 12:30:27) dat ik per dag van de datum range die ik wil controleren zal moeten gaan kijken of er nog kamers beschikbaar zijn op die dag. Of heeft er iemand nog een betere oplossing waarmee dit met één query zou kunnen?
Dit kan al met 1 query.
Hoe? Kun je daar wellicht een voorbeeld van geven?
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
<? select a.Date, (query met a.Date er in) as kamers_vrij
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24' ?>
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24' ?>
Dit is wat ik doe:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
SELECT a.Date, (SELECT count(*)
FROM reservations
WHERE `reservation_end` > a.Date
AND `reservation_start` < a.Date
GROUP BY room_type_id
) as kamers_bezet
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2012-12-20' and '2012-12-25'
?>
SELECT a.Date, (SELECT count(*)
FROM reservations
WHERE `reservation_end` > a.Date
AND `reservation_start` < a.Date
GROUP BY room_type_id
) as kamers_bezet
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2012-12-20' and '2012-12-25'
?>
Wat ik hier nu uit zou willen krijgen is een lijstje dat er als volgt uit ziet:
a.Date: Kamers bezet:
2012-12-20 0
2012-12-21 1
2012-12-22 1
2012-12-23 1
2012-12-24 0
Wat doe ik fout?
denk dat je die where clause iets moet maken where a.Date between reservation_start and reservation_end.
Gewijzigd op 18/12/2012 15:09:20 door No One
Lars Anderson op 18/12/2012 13:29:03:
Hoe? Kun je daar wellicht een voorbeeld van geven?
Het 'put your money where your mouth is' principe. En ik kan je geen ongelijk geven, want waar ik eerst dacht 'dat doe je even zo', bleek het toch wat lastiger.
Maar, zonder overigens geprobeerd te hebben de query van Henze aan de praat te krijgen, heb ik wel iets wat in mijn kleine test omgevinkje werkte. Per stap uitleg:
1) als eerste heb je nodig een rij van data waarover je wil controleren hoeveel reserveringen er zijn. Ik ken iemand die een tabel bijhoudt met alle dagen van nu en in de toekomst erin.... dat ben ik echter niet van plan. Dus hier een 'on-the-fly' oplossing (met een addertje):
Code (php)
1
2
3
4
2
3
4
SET @num = -1;
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
De data kan je zelf invullen, '2013-01-01' en '2013-01-14' waren mijn testwaardes.
Het addertje is in de FROM clause, die tabel 'numbers'. Deze query kan je niet draaien zonder echte tabel en ook niet zonder een tabel met genoeg rijen erin! Numbers is dus een bestaande tabel bij mij, die ook een aantal rijen heeft. Maakt niet uit wat erin staat, als er maar iets bestaat. In mijn geval heeft die tabel 1 kolom, met 100 rijen met alleen 0. Dit heb je nodig, want bovenstaande loopt alle rijen van de geselecteerde tabel af en creeert tijdens die query de data die je nodig hebt. Heb je dus te weinig rijen in de tabel staan, dan krijg je ook te weinig dagen!!
2) link bovenstaande aan de tabel met reserveringen
Hier komt ook een alternatieve methode om de hoek kijken. Bij de join die je maakt wil je eigenlijk de reserveringen dupliceren voor alle dagen van de reservering, zodat je makkelijk kunt tellen:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT a.id, b.date_sequence
FROM booking a
INNER JOIN (
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
) b ON (
a.aankomst <= b.date_sequence
AND vertrek > b.date_sequence
)
FROM booking a
INNER JOIN (
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
) b ON (
a.aankomst <= b.date_sequence
AND vertrek > b.date_sequence
)
Zoals je ziet zijn de voorwaarden voor de join niet de gebruikelijke gelijkheden, maar ongelijkheden. Zo worden de reserveringen gekoppeld aan elke dag in de reservering, niet alleen aan de aankomst en/of vertrekdag.
3) tellen
Het makkelijke deel :-)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SET @num = -1;
SELECT count(a.id), b.date_sequence
FROM booking a
INNER JOIN (
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
) b ON (
a.aankomst <= b.date_sequence
AND vertrek > b.date_sequence
)
GROUP BY b.date_sequence;
SELECT count(a.id), b.date_sequence
FROM booking a
INNER JOIN (
SELECT DATE_ADD( '2013-01-01', interval @num := @num+1 day) AS date_sequence
FROM numbers
HAVING DATE_ADD('2013-01-01', interval @num day) <= '2013-01-14'
) b ON (
a.aankomst <= b.date_sequence
AND vertrek > b.date_sequence
)
GROUP BY b.date_sequence;
Ik hoop dat het duidelijk is, dat het werkt en ook dat het niet al te langzaam is. Het werkte dus in de testomgeving, maar die is te klein om echte performance to kunnen checken.
Gewijzigd op 18/12/2012 16:32:06 door Erwin H
En dan zonder de queries eromheen.
Een andere optie is om simpelweg een tabelletje te maken met de datums voor de aankomende jaren en daarop te joinen bv:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
d.adate,
COUNT(r.id) AS rooms_reserved
FROM
(SELECT adate FROM date_table
WHERE adate BETWEEN '2012-12-19' AND '2012-12-24') d
LEFT JOIN
(SELECT id, reservation_start rs, reservation_end re
FROM reservations
WHERE room_type = 'A' AND reservation_end >= CURRENT_DATE()) r
ON r.rs <= d.adate AND r.re > d.adate
GROUP BY d.adate
d.adate,
COUNT(r.id) AS rooms_reserved
FROM
(SELECT adate FROM date_table
WHERE adate BETWEEN '2012-12-19' AND '2012-12-24') d
LEFT JOIN
(SELECT id, reservation_start rs, reservation_end re
FROM reservations
WHERE room_type = 'A' AND reservation_end >= CURRENT_DATE()) r
ON r.rs <= d.adate AND r.re > d.adate
GROUP BY d.adate
Probleem blijft bestaan dat als er in die periode 2x een verschillende specifieke kamer is gereserveerd je dat niet kunt achterhalen.
Toevoeging op 18/12/2012 16:49:14:
@Erwin, ik ken die persoon toevallig ook!
Gewijzigd op 18/12/2012 16:40:39 door Ger van Steenderen
Ger van Steenderen op 18/12/2012 16:37:20:
@Erwin, ik ken die persoon toevallig ook!
:-)
Nu wil ik niet zeggen dat dat een slechte oplossing is, het is gewoon niet de mijne. Helaas heb ik het nu alsnog moeten doen met een extra (verder nutteloze) tabel, dus ik blijf nog wel even kijken naar een oplossing zonder verdere externe hulpmiddelen.
Normaal gesproken ben ik ook geen voorstander van zo'n nutteloze tabel, maar of het één er met datums is of met nummers maakt ook niet zoveel uit. Overigens kan jou stukje subquery iets makkelijker:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT
@startdate := @startdate + INTERVAL 1 DAY AS datum
FROM
numbers,
(SELECT @startdate := '2012-12-17') uvar
HAVING @startdate <= '2012-12-24'
@startdate := @startdate + INTERVAL 1 DAY AS datum
FROM
numbers,
(SELECT @startdate := '2012-12-17') uvar
HAVING @startdate <= '2012-12-24'