SQL Scheduling - Select All Rooms Available for Given Date Range

SQL Scheduling - Select All Rooms Available for Given Date Range

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.

Select available rooms in given date range

Try this:

SELECT * FROM Rooms WHERE ID NOT IN(SELECT room_id FROM reservations WHERE '2014-03-07 19:00:00' < e_to AND '2014-03-08 19:00:00' > e_from)

mysql query for getting all rooms which are not booked between a date range not giving expected results

You can try:

SELECT r.id AS roomID, r.name, r.no_of_rooms, 
       r.no_of_rooms - COALESCE(t.bookedRooms,0) AS available_rooms
FROM rooms AS r
LEFT JOIN (
   SELECT room_id, SUM(total_rooms) AS bookedRooms
   FROM bookings
   WHERE `start` < '2015-08-14' AND `end` > '2015-08-11'
   GROUP BY room_id ) AS t
ON r.id = t.room_id
WHERE r.no_of_rooms - COALESCE(t.bookedRooms,0) > 0

The key to finding booked rooms for the specified period is the following predicate:

`start` <= '2015-08-12' AND `end` >= '2015-08-11'

The above gets all rooms whose booking period overlaps, even for one day, with the specified period.

Using a GROUP BY on room_id we can get the total sum of booked rooms per room_id for the period specified.

To get the expected result set, we can just do a LEFT JOIN with the derived table of booked aggregates and simply subtract the sum of booked rooms from the number of available rooms.

Demo here

How to select the time a room is available given date and room in SQL Server?

Assuming no overlaps, and minutes as the smallest time unit, and of course the Numbers table, for SQL Server 2012 this should work:

declare @date datetime = '20170102', @room int = 14
;with x as (
    select num.n, isnull(lag(num.n) over(order by num.n),-1) n_prev
    from utility.numbers num
    left join Reservation t on num.n between datediff(minute, @date, reservationdatefrom) and datediff(minute, @date, reservationdateto) and t.reservationroom = @room
    where num.n < 1440 and num.n > 0
    and t.reservationid is null
)
select *, cast(dateadd(minute, n-1, @date) as time), cast(dateadd (minute, isnull(lead(n_prev+1) over(order by n), 1440), @date) as time),
isnull(lead(n_prev+1) over(order by n), 1440) - (n-1)
from x 
where n <> n_prev+1

(A solution for 2008 would include self-joins. Very ugly IMO).

For more on Numbers table, check here:

https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

SQL query to select all rooms from a table which still have free capacity in date range

I think this is pretty close:

SELECT r.id, r.name, r.room_category_id, r.status, r.capacity, r.capacity - IFNULL(COUNT(res.id), 0) AS beds_available
FROM room AS r
LEFT JOIN reservation_room as rr ON rr.room_id = r.id
LEFT JOIN reservation as res
     ON res.id = rr.reservation_id
     AND @range_start < res.end
     AND @range_end > res.start
GROUP BY r.id

The only problem is if there are two non-overlapping reservations for the same room during the period, it will decrease the available beds twice. I'm not sure how to sort all the reservations so that only overlapping reservations add to the bed use count.

check room availability with date range

This will select all rooms that have no row with status=1 in the interval you select. Notice that I am not using between: if status=1 on 13th of December, the room is still available in the interval, that's why I'm using >= and <:

SELECT roomid
FROM status
WHERE `date` >= '2012-12-10'
      AND `date` < '2012-12-13'
GROUP BY roomid
HAVING sum(status.status=1)=0

If there could also be some missing days in your table, and if that means that the room is not booked but also not available, you could also use this query:

SELECT roomid
FROM status
WHERE `date` >= '2012-12-10'
      AND `date` < '2012-12-13'
      AND status=0
GROUP BY roomid
HAVING count(*)=DATEDIFF('2012-12-13', '2012-12-10')

that checks that the number of days in the interval is equal to the number of rows with status=0.

Get available room between two dates when there are multiple category in one booking

You just need a simple range intersection query. This should do it:

SELECT category, COUNT(*)
FROM room
WHERE NOT EXISTS (
    -- room is booked on the requested dates (...not)
    SELECT 1
    FROM reservedRoom
    JOIN bookings ON reservedRoom.bookingID = bookings.bookingID
    WHERE reservedRoom.roomID = room.roomID
    AND '2018-02-09' > checkIndate
    AND '2018-02-08' < checkOutDate
)
GROUP BY category

SQL for finding free rooms during a specific time period

The logic for a not-free room is:

• The booking starts on or before the period in question ends.
• The booking ends on or after the period in question starts.

A not not-free room is available. Let's apply this logic to your query:

SELECT r.id, r.num, r.name, h.name, h.city, h.country
FROM room r JOIN
     hotel h
     ON r.hotel_id = h.id JOIN
     room_type rt
     ON r.room_type_id = rt.id
WHERE rt.id = $roomtype AND
      h.id = $hotel AND
      r.id NOT IN (SELECT res.room_id 
                   FROM reservation res
                   WHERE '$from' <= end_date AND
                         '$to' >= start_date AND
                  );

Note: You should be using parameters for values in your query, rather than munging query strings.

---

Related Topics