MySQL query to select all hostels with at least X spaces between start and end dates?

Question

I have 2 tables, one with hostels (effectively a single-room hotel with lots of beds), and the other with bookings.

Hostel table: unique ID, total_spaces
Bookings table: start_date, end_date, num_guests, hostel_ID

I need a (My)SQL query to generate a list of all hostels that have at least num_guests free spaces between start_date and end_date.

Logical breakdown of what I'm trying to achieve:

For each hostel:

1. Get all bookings that overlap start_date and end_date
2. For each day between start_date and end_date, sum the total bookings for that day (taking into account num_guests for each booking) and compare with total_spaces, ensuring that there are at least num_guests spaces free on that day (if there aren't on any day then that hostel can be discounted from the results list)

Any suggestions on a query that would do this please? (I can modify the tables if necessary)

Answer 1

I built an example for you here, with more comments, which you can test out: http://sqlfiddle.com/#!9/10219/9

What's probably tricky for you is to join ranges of overlapping dates. The way I would approach this problem is with a DATES table. It's kind of like a tally table, but for dates. If you join to the DATES table, you basically break down all the booking ranges into bookings for individual dates, and then you can filter and sum them all back up to the particular date range you care about. Helpful code for populating a DATES table can be found here: Get a list of dates between two dates and that's what I used in my example.

Other than that, the query basically follows the logical steps you've already outlined.

Answer 2

Ok, if you are using mysql 8.0.2 and above, then you can use window functions. In such case you can use the solution bellow. This solution does not need to compute the number of quests for each day in the query interval, but only focuses on days when there is some change in the number of hostel guests. Therefore, there is no helping table with dates.

with query as
(
  select * from bookings where end_date > '2017-01-02' and start_date < '2017-01-05'
)
select hostel.*, bookingsSum.intervalMax
from hostel
join 
(
  select tmax.id, max(tmax.intervalCount) intervalMax
  from
  (
      select hostel.id, t.dat, sum(coalesce(sum(t.gn),0)) over (partition by t.id order by t.dat) intervalCount
      from hostel
      left join
      (
        select id, start_date dat, guest_num as gn from query 
        union all
        select id, end_date dat, -1 * guest_num as gn from query
      ) t on hostel.id = t.id
      group by hostel.id, t.dat
  ) tmax
  group by tmax.id
) bookingsSum on hostel.id = bookingsSum.id and hostel.total_spaces >= bookingsSum.intervalMax + <num_of_people_you_want_accomodate>

demo

It uses a simple trick, where each start_date represents +guest_num to the overall number of quests and each 'end_date' represents -guest_num to the overall number of quests. We than do the necessary sumarizations in order to find peak number of quests (intervalMax) in the query interval.

You change '2017-01-05' in my query to '2017-01-06' (then only two hostels are in the result) and if you use '2017-01-07' then just hostel id 3 is in the result, since it does not have any bookings yet.