如何查看在2日期之间可用的房间 [英] How to look which room is available between 2 dateTimes

问题描述

我正在使用可以预订会议室的网络应用程序。让我先解释一下数据库的样子。

我有表预订 >

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

我还有一个表 ROOM 以下设计

  ID  - > int 
 NAME  - > VARCHAR（30）
  

现在在表中插入一行 reservation 看起来像这样

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

现在我要做的是，输入开始日期和结束日期。当我按下一个按钮查看空房情况返回此指定日期范围内可用的所有房间名称。

此查询：

  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 
  

我知道还有很多缺失，但问题是什么。

任何人都可以帮助吗？

$ p

解决方案

其他答案不处理重叠预订的时间请求，BETWEEN不是一个好选择因为它是包容性的（因此，如果预订在下午2点结束，并且你想要一个从2点开始，它将是假的，这是不正确的）。

试试这个SQL小提琴。

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

摘录：

  select * from Room as ro 
其中ro.ID不在
 （
选择re.ROOMID 
从保留作为
其中（DATE_BEGIN> = @start和DATE_BEGIN< @end）
或（DATE_END> = @start和DATE_END < @end）
）
  

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.

解决方案

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

这篇关于如何查看在2日期之间可用的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！