hotel reservation system SQL query: identify when one specific room is available

I have a query for a hotel reservation system and I need to find dates when a specific room is available. (It's a boutique hotel where people reserve specific rooms, so they know the exact room they want before they get to this code. The result I'm after is the detail for ONE room, the one I specify in the query-- I am not looking for info on multiple rooms.)

The 'availability' table schema is simple, each row is:

room_id
date_occupied - a single day that is occupied (like '2011-01-01')

So, if, for example, a room is occupied from January 1 to January 5, five rows are added to the availability table, one for each day the room is occupied.

Here's what I'm trying to work out:

• the query to find when a specific room is available between a start and end date, sort of like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND nothing between start_date and end_date is in availability.date_occupied

• I'm also seeking a similar query where I just want to see if a specific room is available for the start date and the following two days, something like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND start_date, start_date+1day and start_date+2days is not in availability.date_occupied
开发者_StackOverflow

I'm a bit stuck trying to figure out the exact joins. Any suggestions? Note that if it helps, I'm totally open to a different schema for the availability table. Whatever makes the queries work most efficiently.

I think the best way to do this is to simply run a query to see if a room is occupied and indicate availability in the event that no rows are returned.

As such, for you first query:

SELECT COUNT(id) AS occupied_days FROM availability
WHERE room_id = 123
      AND date_occupied BETWEEN @start_date AND @end_date;

If occupied_days == 0 then the room is available.

For your second query, just replace @end_date with DATE_ADD(@start_date, @num_days)

Just some comments on answers involving a join: since you're limiting your search to a specific room_id, it makes no sense to join the availability table to the room table, since it's providing no necessary information. All these LEFT JOINs are just complicating the query, potentially impairing performance and providing nothing of any use.

Also, while you may baulk at the approach of searching for occupancy and then inferring availability, I would guess that this query is by far the fastest and easiest for the query engine to optimize, since it's based on a single column which, if indexed, will make things even faster.

I've done something similar, but for All rooms available within a given range... I've modified the concept for yours since yours is a specific room, but heres the original link Link to other registration

The premise is to have a dynamically created "date range list" without having to actually create a table and explicitly insert the rows. This can be done by using MySQL query variables. I query from a(any) table with a limit command to how many entries I need.. your reservation table should be fine... Then, I query from that via the specific dates that are created where the room is NOT found...

select  JustDates.OpenDate
    from 
        ( SELECT 
                 @r:= date_add(@r, interval 1 day ) OpenDate
            FROM 
                 (select @r := current_date()) vars,
                availability limit 30 ) JustDates
    where
        JustDates.OpenDate not in
           ( select date_occupied 
                 from availability
                 where availability.room_id = OneYouWant
                   and availability.date_occupied = JustDates.OpenDate )
    order by 
       JustDates.OpenDate

If you want the room information, you can just get a Cartesian join against it since you already know the room, and all would be the same anyhow...

select Rooms.*
    from Rooms,
         ( AboveQuery ) OpenDates
    where Rooms.Room_ID = OneYouWant

You can check the link to the other solution for more clarification on how the @r starting from the "current_date()" gets initialized, and the LIMIT 30 lets it go out for 30 days as a result of the Cartesian to the availability table. Each new record in the LIMITed 30 records will keep updating the @r to 1 more day. So the inner pre-gets in this example the next 30 days, THEN the WHERE NOT IN goes to the availability table for the specific room and qualified dates dynamically built... This would only return the dates NOT already occupied.

Hopefully this clarifies the technique for you.

(Here's my updated answer based on the OP's clarification of the criteria)

Eric, take a look at the following:

My Test Schema

mysql> desc rooms;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.01 sec)

mysql> desc availability;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| rooms_id      | int(11) | NO   | MUL | NULL    |       |
| occupied_date | date    | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

My Test Data

mysql> select * from rooms;
+-----+
| id  |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

mysql> select * from availability;
+----------+---------------+
| rooms_id | occupied_date |
+----------+---------------+
|      123 | 2011-05-04    |
+----------+---------------+
1 row in set (0.00 sec)

My Test Queries

mysql> select rooms.* from rooms left outer join availability on rooms.id = availability.rooms_id where rooms.id=123 and availability.occupied_date not between '2011-05-03' and '2011-05-06';
Empty set (0.00 sec)

mysql> select rooms.* from rooms left outer join availability on rooms.id = availability.rooms_id where rooms.id=123 and availability.occupied_date not between '2011-05-05' and '2011-05-06';
+-----+
| id  |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

The first test query shows a user searching for room 123 between the dates of 5/3 and 5/6, when the room is occupied. The second query shows a user searching for room 123 between the dates of 5/5 and 5/6, when the room is available.

The missing ingredient here was a LEFT OUTER join. This returns values from "table A" (rooms) whether or not corresponding records in "table B" (availability) exist. You're only wanting cases where they don't exist in availability, so to ensure this, you should include a column in availability in your result records, and specify a WHERE clause that requires that availability.xxx IS NULL. This last bit is missing from my test/example queries, so give it a try on your end and let me know if you still need help.

SQL 2008 Solution

create table availability
(
room_id int,
date_occupied datetime
)
go

insert into availability values(212,convert(date,DATEADD(d,2,GETDATE())));
insert into availability values(202,convert(date,DATEADD(d,5,GETDATE())));
insert into availability values(209,convert(date,DATEADD(d,7,GETDATE())));
insert into availability values(212,convert(date,DATEADD(d,9,GETDATE())));
insert into availability values(202,convert(date,DATEADD(d,10,GETDATE())));
insert into availability values(202,convert(date,DATEADD(d,20,GETDATE())));
go

CREATE FUNCTION GetMonthTable
(
    @d datetime
) RETURNS @days TABLE
(
    [Date] datetime,
    [Day] varchar(20)
)
BEGIN
    DECLARE @d1 datetime, @d2 datetime, @d3 datetime
    SELECT
    @d1 = DATEADD(mm, DATEDIFF(mm, 0, @d), 0),
    @d2 = DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @d) + 1, 0))

    WHILE @d1 <= @d2
    BEGIN
    INSERT INTO @days SELECT @d1, DATENAME(DW, @d1)
    SELECT @d1 = DATEADD(dd, 1, @d1)
    END
    RETURN
END
go

select A.Date,
       A.Day, 
       convert(bit,isnull((B.date_occupied-B.date_occupied),1))Avalilability 
from        GetMonthTable(getdate()) A
left join   (Select * from availability where room_id=212) B
on A.Date=b.date_occupied

Sorry for the formating. I am new to this site

Sounds easy enough do an inner join on rooms and availability. Please forget about the 'where' type implicit join and use an explicit join. That way the query is easier to read because you separate the join condition and the selection criteria.

First make sure you have an index on the field availability.room_id And make sure the rooms.id is the primary key on rooms.

select * from rooms where id not in
  (select rooms.id from rooms
   inner join availability on (rooms.id = availability.room_id)
   where availability.date_occupied between StartDate and EndDate)
and rooms.id = 123

If you want to do further test you can add an extra clauses.

...Previous query +
  and rooms.smoking = 0
  and rooms.number_of_beds = 2

etc.

Query to find when a specific room is available between a start and end date:

SELECT r.*
FROM rooms r
  LEFT JOIN availability av
    ON av.room_id = r.id
WHERE r.id = 123
  AND av.date_occupied BETWEEN start_date AND end_date
GROUP BY r.id
HAVING count(av.room_id) = 0 

Another way:

SELECT *
FROM rooms
WHERE id = 123
  AND NOT EXISTS
    ( SELECT 1
      FROM availability
      WHERE date_occupied BETWEEN start_date AND end_date
        AND room_id = 123
    ) 

For your second question, just change the end_date to

DATE_ADD( start_date, INTERVAL 2 DAY)

or to

(start_date + INTERVAL 2 DAY)

Your queries are simple if you keep a calendar table in your database. Have a look in this answer for a minimal example of how to create a table of dates. The rest of this answer assumes the existance of that particular table.

the query to find when a specific room is available between a start and end date:

select datum as available_date
  from calendar c
 where c.datum between date '2011-01-01' and date '2011-01-10'
   and c.datum not in(
        select occupied_date 
          from availability
         where room_id = 1);

That query will return all the dates within your specified range where room_id=1 isn't occupied.

You can extend the above query to find:
if a specific room is available for the start date and the following two days

select case when count(*) = 3 then 'Yes' else 'Nope' end as available
  from calendar c
 where c.datum between date '2011-01-06' and date '2011-01-06' + interval '2' day
   and c.datum not in(
        select occupied_date 
          from availability
         where room_id = 1);

That query will return 'Yes' when none of the days within the specified data range are occupied. The number 3 corresponds to the number of days within the date range (start day + 2 days).

A final note/warning: The above queries will not work if you supply a non-existing room_id. This isn't a problem if your application builds the SQL.