Geek asks and answers

How to see which room is available between 2 dateTimes

I am working on a web application where you can reserve a conference room. Let me first explain to you what my database looks like.

I have a table Reservationswith the following design

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

I also have a table ROOMwith the following design

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

Now inserting a row in the table reservationlooks 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'm doing, I entered the start date and end date. And when I click the button, for example. Check availabilityit returns all room names that are available for a given date range.

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, but the problem is that. I do all this in a function called by a web service.

Can anyone help?

Sincerely.

+3
5

, , BETWEEN , (, 2 , , 2 , false, ).

SQL. , , , .

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

:

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)
             )
+3

BETWEEN

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

, / , , / ( LEFT OUTER JOIN, , , 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))
0

, @DATUM_BEGIN @DATUM_EINDE. @DATUM_BEGIN res_DATUM_EINDE, @DATUM_BEGIN > res_DATUM_EINDE @DATUM_EINDE res.DATUM_BEGIN, @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
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))
0

More articles:


All Articles