1

I'm using Microsoft's idea for storing resource and booking information. In short, resources, such as a hotel room, do not have date records and booking records have a BeginDate and EndDate.

I'm trying to retrieve room availability information using MS's queries but something tells me that MS's queries leave much to be desired. Here's the MS article I'm referring to: http://support.microsoft.com/kb/245074

How can I retrieve available rooms for a given date range? Here's my query that returns a simple list of bookings:

SELECT r.RoomID, b.BeginDate, b.EndDate
FROM tblRoom as r INNER JOIN tblBooking b ON r.RoomID = b.AssignedRoomID;

But I'm still baffled as to how I can get a list of available rooms for a given date range?

I am using Microsoft Access but I'd like to keep my queries DBMS agnostic, as much as possible. While this isn't really my question, if you feel that the data model I'm using is unsound, please say so, as I am willing to consider a better way of storing my data.

Edit1:
I failed to mention that I don't like MS's queries for two reasons. First of all, I'm really confused about the 3 different OR operators in the WHERE clause. Are those really necessary? Secondly, I don't like the idea of saving a query and using it as a table although I'm willing to do that if it gets the job done, which in this case I believe it does.

Edit2:
This is the solution I've landed on using the excellent answer given here. This is MS Access SQL dialect (forgive me): 

SELECT * FROM tblRoom AS r  
WHERE RoomID NOT IN  
 (SELECT AssignedRoomID as RoomID From tblBooking  
  WHERE assignedroomid IS NOT NULL AND assignedroomid = r.roomid AND 
    (BeginDate < @BookingInquiryEndDate AND EndDate > @BookingInquiryBeginDate)  
 )
HK1
  • 11,941
  • 14
  • 64
  • 99
  • Well you can use BETWEEN i suppose – Ruben May 24 '11 at 13:38
  • So, is an available room one with _no_ bookings within the specified date range? – Rob Cowie May 24 '11 at 13:39
  • Which SQL engine are you using???? MySQL, SQL-Server, Oracle? – DRapp May 24 '11 at 13:40
  • DRapp, read towards the bottom of the question. I already have that in there!!!!!!!!!!!!!!!!!!!!!!!!!!!!! – HK1 May 24 '11 at 13:41
  • Rob Cowie, yes, I think under this data model that is correct. An available room is one with no bookings during the specified date range. Overlap is allowed on BeginDate and EndDate since one guest can checkout in the AM and another checkin in the afternoon/evening. – HK1 May 24 '11 at 13:42
  • SELECT r.RoomID FROM tblRoom where (date>=b.BeginDate AND date<=b.EndDate) – Naresh May 24 '11 at 13:43
  • If there is time problem use another field as CHECK with 0 as vacant and 1 as full. – Naresh May 24 '11 at 13:48
  • illuminati, where's your join statement? or were you just oversimplifying? – HK1 May 24 '11 at 13:48
  • Why all these?? Just create one table with roomid,joindt,enddt,chkvalue as fields that will do the work if I'm not wrong. – Naresh May 24 '11 at 13:54
  • You might want to check performance of NOT EXISTS versus the NOT IN that you currently have. – Tom H May 24 '11 at 14:36
  • Thanks Tom. MS Access does not support NOT EXISTS. It only supports NOT IN. I think it might be the same thing, only a difference in dialect but I don't know for sure. – HK1 May 24 '11 at 14:40

1 Answers1

7

You want all the rooms which do not have a booking in that date range, i.e., If your sql engine does subqueries... 

Select * From Rooms r 
where not exists 
     (Select * From Bookings 
      Where room = r.room
         And startBooking < @endRange
         And endBooking > @startRange)

HIK, to understand the need for the room = r.room clause try these two queries

Query One (with room = r.room clause)

       Select r.*,
       Case Where Exists
           (Select * From Bookings 
            Where room = r.room
                 And startBooking < @endRange
                 And endBooking > @startRange) 
        Then 'Y' Else 'N' End HasBooking
    From Rooms r

Query Two(without room = r.room clause) 

    Select r.*,
       Case Where Exists
           (Select * From Bookings 
            Where startBooking < @endRange
                 And endBooking > @startRange) 
        Then 'Y' Else 'N' End HasBooking
    From Rooms r

Notice the first one returns different values in HasBooking for each row of the output, because the subquery is 'Correleated' with the outer query... it is run over and over agaio, once for each outer query results row.

The second one is the same value for all rows... It is only executed once, because nothing in it is dependant on which row of the outer query it is being generated for.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • This looks like what I'm looking for. I'll give it a whirl. – HK1 May 24 '11 at 13:49
  • Do I really need this where condition: room = r.room ?? I'm trying to figure out what it does. – HK1 May 24 '11 at 13:51
  • Yes, else it will consider bookings for _all_ rooms, which would cause all rooms to appear booked even if only one is – Rob Cowie May 24 '11 at 13:53
  • Possibly too much info, but if you need to handle open-ended bookings, replace `endBooking > @startRange` with `(endBooking > @startRange OR endBooking IS NULL)` – Rob Cowie May 24 '11 at 13:55
  • Charles, Rob, I guess I'm still baffled by room=r.room. The problem is that in MS Access my subquery is unaware of the table/entity r since r is in the parent/main query. – HK1 May 24 '11 at 14:03
  • @HK1: According to http://msdn.microsoft.com/en-us/library/bb208942(v=office.12).aspx, the subquery _can_ refer to table aliases that are defined in the outer query. It should work. – Rob Cowie May 24 '11 at 14:39
  • Thanks, Rob. I must have had something else wrong in my query when I tried it. I got it working now using assignedroomid = r.roomid. I admit I'm still baffled about the necessity of it. I guess I just don't comprehend your explanation above. – HK1 May 24 '11 at 14:42
  • HK, This causes the subquery, for each room in the results, to return true when there exists a booking for that specific room mentioned in the outer query... Otherwise, for EVERY row in the output, it would return true if ANY room had a booking in your date range – Charles Bretana May 24 '11 at 23:03