0

I have an availability table ind Mysql as follows:

id
room_id int(11)
avail_date date

For each room, there is a row for every date it is available. It is possible that there are gaps, for example room 1 might have entries for 1,2,3,5,6,13,14,15 of august, every other day it is not available.

I need a query to find a list of room_ids where there is availabilty for every day within a date range.

In other words, get a list of room_ids where there is an entry for each room for each date between startdate and enddate.

patterncatcher
  • 275
  • 2
  • 9
  • 2
    typically you should post what you have tried. This hotel question is a common one - like homework common, and this is not a do my homework site. – Randy Sep 03 '13 at 11:38
  • Even if it's homework, you'll get help if the question is properly asked: just be honest about it! :) – STT LCU Sep 03 '13 at 11:46
  • I´m sorry if I offended anyone with my question, I just didn´t have a clue where to start trying to find the rooms with entries for consecutive days between two dates – patterncatcher Sep 03 '13 at 12:10

1 Answers1

2

You want something like this:

select room_id
from availability a
where avail_date between $start and $end
group by room_id
having count(*) = datediff($end, $start) + 1;

The having clause is counting the number of rows during that period to see if it matches the number of days you need. This is "inclusive" logic, so if $start = $end, then it assumes you need the room on that date.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786