Continous date availability query in MySQL?

Question

I have a table in my db, which contains following data:

————————————————————————————————————————————————————————————————————————
Id          startDate          availabilityStatus        Hotel_Id
————————————————————————————————————————————————————————————————————————
1          2016-07-01             available                 2
2          2016-07-02             available                 2
3          2016-07-03             unavailable               2
4          2016-07-04             available                 3
5          2016-07-05             available                 3
6          2016-07-06             available                 3
7          2016-07-07             unavailable               4
8          2016-07-08             available                 4
9          2016-07-09             available                 4
10         2016-07-10             available                 4

Now, user wants to see all the Hotels which have 3 continuous days availability in July’16.

I am able to make the query to get the availability, but not sure how to fetch the Continuous date availability.

As per the above data, in July only Hotel Id 3, 4 have the continuous available dates, but as 2 also have the dates available. so how should we remove 2 and show just 3, 4 via MySQL query.

Please advise?

Answer 1

You can use the following query:

SELECT DISTINCT t1.hotel_id
FROM mytable AS t1
JOIN mytable AS t2 
   ON t1.hotel_id = t2.hotel_id AND 
      DATEDIFF(t1.startDate, t2.startDate) = 2 AND
      t1.availabilityStatus = 'available' AND
      t2.availabilityStatus = 'available'
LEFT JOIN mytable AS t3 
   ON t1.hotel_id = t3.hotel_id AND 
      t3.startDate < t2.startDate AND t3.startDate > t1.startDate AND 
      t3.availabilityStatus = 'unavailable'  
WHERE t3.hotel_id IS NULL   

The query is written in such a way, so that it can easily be adjusted in order to accommodate longer availability periods.

Edit:

Here's a solution using variables:

SELECT DISTINCT hotel_id
FROM (
  SELECT hotel_id,
         @seq := IF(@hid = hotel_id, 
                    IF(availabilityStatus = 'available', @seq + 1, 0),
                    IF(@hid := hotel_id, 
                       IF(availabilityStatus = 'available', 1, 0),
                       IF(availabilityStatus = 'available', 1, 0))) AS seq
  FROM mytable
  CROSS JOIN (SELECT @seq := 0, @hid := 0) AS vars
  ORDER BY hotel_id, startDate) AS t
WHERE t.seq >= 3  

You can test it with your actual data set and tell us how it compares with the first solution.

Answer 2

Try something like that. It works for any number of days. Replace N with 3.

SELECT DISTINCT A.Hotel_Id FROM table A
WHERE 
   A.availabilityStatus = 'available' AND
   N-1 = (
      SELECT count(DISTINCT startDate) FROM table B
      WHERE B.availabilityStatus = 'available' 
        AND A.Hotel_Id = B.Hotel_Id
        AND B.startDate 
              BETWEEN DATE_ADD(A.startDate, INTERVAL 1 DAY) 
                  AND DATE_ADD(A.startDate, INTERVAL N-1 DAY) 
   )

It works like that: for each available date, count available dates in N-1 next days. If their count is N-1, add hotel_id to results.