How to look which room is available between 2 dateTimes

Question

I am working on a web app where it's possible to make a reservation of a meeting room. Let me first explain you how my database looks like.

I have table Reservations with the following design

  ID           --> int
  ROOMID       --> int
  DATE_BEGIN   --> DATETIME
  DATE_END     --> DATETIME

I have also a table ROOM with the following design

 ID            --> int
 NAME          --> VARCHAR(30)

Now an insert of a row in the table reservation looks like this

 ID            --> 1
 ROOMID        --> 2
 DATE_BEGIN    --> 2012-01-01 12:02:33
 DATE_END      --> 2012-01-01 14:00:00

Now what I do is, I enter a begin date and an end date. And when I push a button e.g. Check availability it gives back all the room names which are available for this given date range.

For now I have this query:

SELECT zaa.NAME 
FROM ARTICLES_ZAAL zaa
INNER JOIN ARTICLES_RESERVERING res
ON zaa.ID =res.ZAALID
WHERE res.DATUM_BEGIN <> @DATUM_BEGIN
AND res_DATUM_EINDE <> @DATUM_EINDE

I know that there is still a lot missing but the problem is what. I am doing all of this in a function which is called by a web service.

Could anybody help please ?

kind regards.

What database system, and which version?? **SQL** is just the Structured Query Language - a language used by many database systems... — marc_s, May 09 '12 at 07:03
I am using a custom made database system which is developed for certain software called SIM — Steaphann, May 09 '12 at 07:05
You might want to take a look at this SO question: [determine whether two date ranges overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964). — Nikola Markovinović, May 09 '12 at 07:49

moribvndvs · Accepted Answer · 2012-05-09T07:34:21.263

4

The other answers do not handle requests for time that overlap reservations, and BETWEEN is not a good choice because it is inclusive (so if the reservation ended at 2 PM, and you want one that starts at 2 PM, it would be false, which is not correct).

Try this SQL fiddle. You can mess around with the requested start and end dates to check my work, which is working properly as far as I'm concerned.

http://sqlfiddle.com/#!3/cb682/25

Excerpt:

select * from Room as ro
where ro.ID not in 
            (
              select re.ROOMID
              from Reservations as re 
              where (DATE_BEGIN >= @start and DATE_BEGIN < @end)
                or (DATE_END >= @start and DATE_END < @end)
             )

edited May 09 '12 at 07:34

answered May 09 '12 at 07:26

moribvndvs

42,191
11
135
149

It doesn't work when, for example, second select return null. – Akram May 19 '12 at 15:46
Thanks! That helped me – Tia Mar 14 '16 at 11:47
i think this condition needs to be added to where clause `or (res.end_date >= @end and res.start_date <= @start)` – Tartar Jan 08 '18 at 09:07

score 1 · Answer 2 · answered May 09 '12 at 09:33

1

try using BETWEEN

select * from rooms 
where roomid not in(select roomid from reservation where begin_date between given_begin_date and end_date

answered May 09 '12 at 09:33

Okky

10,338
15
75
122

hkf · Answer 3 · 2012-05-09T07:10:47.603

You'll want to capture the fact that for each given start/end range, you have a set of existing reservations and the condition is that the given start/end cannot fall in the set of existing reservations (using a LEFT OUTER JOIN, so if the rooms are not reserved the reservation dates should be NULL):

SELECT DISTINCT zaa.NAME  
FROM ARTICLES_ZAAL zaa 
LEFT OUTER JOIN ARTICLES_RESERVERING res 
ON zaa.ID = res.ZAALID 
WHERE ((res.DATUM_BEGIN NOT BETWEEN @DATUM_BEGIN AND @DATUM_EINDE OR (res.DATUM_BEGIN IS NULL))
AND ((res.DATUM_EINDE NOT BETWEEN @DATUM_BEGIN AND @DATUM_EINDE) OR (res.DATUM_EINDE IS NULL))

Ok I will take a look at this answer and let you know if it worked. — Steaphann, May 09 '12 at 07:22

bitoshi.n · Answer 4 · 2012-05-09T07:32:00.530

To know unreserved room where you have @DATUM_BEGIN and @DATUM_EINDE. Then you have to check intersection of @DATUM_BEGIN against res_DATUM_EINDE where @DATUM_BEGIN > res_DATUM_EINDE and @DATUM_EINDE against res.DATUM_BEGIN where @DATUM_EINDE < res.DATUM_BEGIN.

SELECT zaa.NAME 
FROM ARTICLES_ZAAL zaa
INNER JOIN ARTICLES_RESERVERING res
ON zaa.ID =res.ZAALID
WHERE MAX(res_DATUM_EINDE)<@DATUM_BEGIN
GROUP BY res.ZAALID
INTERSECT
SELECT zaa.NAME 
FROM ARTICLES_ZAAL zaa
INNER JOIN ARTICLES_RESERVERING res
ON zaa.ID =res.ZAALID
WHERE MIN(res.DATUM_BEGIN)>@DATUM_EINDE
GROUP BY res.ZAALID

Vetrivel mp · Answer 5 · 2012-05-09T09:42:12.013

0

select * from rooms where roomid not in(select roomid from reservation where (begin_date>=given_begin_date and begin_date<=given_end_date ) or(end_date>=given_begin_date and end_date<=given_end_date))

edited May 09 '12 at 09:42

answered May 09 '12 at 07:28

Vetrivel mp

1,214
1
14
29

How to look which room is available between 2 dateTimes

5 Answers5

Linked

Related