在两个日期之间选择 [英] Select between two date

问题描述

嗨！

我有两个桌子，房间"和忙".我的繁忙表包含预订记录，其中包含到达日期和离开日期.

I have two tables, "room" and "busy". My busy table contains reservation records containing an arrival date and a departure date.

我希望选择一个在给定两个日期后都没有预订的房间.

某人给了我他要保留房间和打算离开的日期，从那以后我希望能够显示他可以使用的房间.

Someone gives me the date to which he wants to reserve his room and to which he intends to leave, from that I want to be able to show the rooms that are available to him.

忙碌表=> | IdRoom || ArrivalDate | DepartureDate |

Busy Table => |IdRoom||ArrivalDate|DepartureDate|

房间表=> | IdRoom | NumRoom |等等..|

Room Table => |IdRoom|NumRoom|And so on..|

我尝试了不同的查询，但是我什么也没得到.

I tried different queries but I don't get anything.

我经过测试的查询:

SELECT Room.*, Busy.* 
FROM Room
INNER JOIN Busy ON Room.RoomdId= Busy.RoomdId
WHERE (@dateArr NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate') 
AND (@dateDep NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate')

谢谢您的帮助！

推荐答案

好，所以我已更正了我的查询.

Ok so i've corrected my query.

这是我使用的查询:

SELECT * 
FROM Room 
WHERE RoomId in (
             SELECT RoomID FROM Reservation WHERE EndDate <= @startDate OR  
             StartDate >= @endDate
             )

当然有更好的方法可以做到，但确实可以. 谢谢大家的帮助；)

There is surely a better way to do it but it does the job. Thank you all for helping me ;)

这篇关于在两个日期之间选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！