Prashant Prashant - 6 months ago 13
PHP Question

Continous date availability query in MySQL?

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

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.