'Arftul Software' 'Find sequence starts and ends' with Dates

Question

EDIT: I've changed the title from "Find Cheapest Available Dates Based on Min and Max Date and Duration - MySQL PHP" in order to make the question more generic in the hope that other people with this problem will be able to find the solution. Below is the full content of my original question but here I will re-write it because now I understand what the problem was, I can better explain it. I've also added the answer myself.

RE-WRITTEN QUESTION

The Artful Software website has an example of a query called 'Find sequence starts and ends' which I am hoping to use in order to find sequences of dates where a room is available, however for some reason the query breaks up sequential results which cross from one month into the next. For example if a room is available between 28th June and 3rd July, it would return two results:

28th - 30th June
1st - 3rd July

When it should return just one result for those dates since they are sequential. Why is this happening and how can I prevent it?

ORIGINAL DETAILED QUESTION

Given a minimum arrival date, maximum departure date and stay duration, I want to find which rooms are available any time in that period and return the cheapest dates.

For example. A user wants to stay for 14 nights any time between 25th May and 30th June. Firstly I need to check which of my rooms have at least 14 consecutive available nights available between 25th May - 30th June and then for each room that has, I want to show which dates would be the cheapest time to stay for 14 nights.

I have:

• a table of dates
• a table of rooms each with a unique Id
• a table of rates which have a roomId, start and end dates and a price (prices are set in date ranges and then there is one standard price for each calendar year and this price is used for any dates which aren't in one of the set rate date ranges)
• a table of bookings with a roomId, start and end dates.

Using the samples at artfulsoftware.com (particularly this one. Scroll down to the 'Forum contributor "laptop alias" posted this solution' section to see an example of the joins I've used) I've come up with this so far. This is my attempt to find which rooms have at least the correct amount of consecutive available dates; it doesn't make any attempt to then find the cheapest:

SELECT r.`id`
FROM `rooms` r
RIGHT JOIN(
  SELECT a.roomId, a.date AS startdate, MIN( c.date ) AS enddate, DATEDIFF(MIN( c.date ), a.date)+1 AS nights
  FROM (
    SELECT r.id AS roomId, d.date AS date
    FROM dates AS d
    CROSS JOIN rooms AS r WHERE
    NOT EXISTS ( SELECT id, roomId
      FROM bookings AS b
      WHERE d.date BETWEEN b.start_date AND b.end_date
      AND r.Id = roomId )
    AND d.date BETWEEN :minArr1 AND :maxDept1
  ) AS a

  LEFT JOIN(
    SELECT r.id AS roomId, d.date AS date
    FROM dates AS d
    CROSS JOIN rooms AS r WHERE
    NOT EXISTS (
      SELECT id, roomId
      FROM bookings AS b
      WHERE d.date BETWEEN b.start_date AND b.end_date
      AND r.Id = roomId
    )
    AND d.date BETWEEN :minArr2 AND :maxDept2
  ) AS b
  ON a.date = b.date + 1 AND a.roomId = b.roomId

  LEFT JOIN (
    SELECT r.id AS roomId, d.date AS date
    FROM dates AS d
    CROSS JOIN rooms AS r WHERE
    NOT EXISTS (
      SELECT id, roomId FROM bookings AS b
      WHERE d.date BETWEEN b.start_date AND b.end_date
      AND r.Id = roomId
    )
    AND d.date BETWEEN :minArr3 AND :maxDept3
  ) AS c
  ON a.date <= c.date AND a.roomId = c.roomId

  LEFT JOIN (
    SELECT r.id AS roomId, d.date AS date
    FROM dates AS d
    CROSS JOIN rooms AS r WHERE
    NOT EXISTS (
      SELECT id, roomId
      FROM bookings AS b
      WHERE d.date BETWEEN b.start_date AND b.end_date
      AND r.Id = roomId
    )
    AND d.date BETWEEN :minArr4 AND :maxDept4
  )AS d ON c.date = d.date - 1 AND c.roomId = d.roomId

  WHERE b.date IS NULL
  AND c.date IS NOT NULL
  AND d.date IS NULL
  GROUP BY a.date, a.roomId
)AS results
on results.roomId = r.Id
WHERE nights >= :n

Now the problem I have is that for some reason, this breaks consecutive dates up when we reach a month end. Say for example a room is available all the way from 25th May to 14th June, instead of pulling back one result with 20 nights it will bring back two:

• 25th - 31st May (6 nights)
• 1st - 14 June (13 nights)

I don't know why it's doing it or how to fix it, but I think if I can get a solution to this problem it will get the first half of my problem sorted. The second will be then to find the cheapest set of 14 nights in the returned period by looking in the rates table, but one thing at a time.

Answer 1

Turns out that after lots of head scratching, the solution is simple. The example on the Artful Software page works because he's only dealing with sequential numbers, not dates. In order to work with dates you must change the +1 and -1 in order to add and subtract a date instead. Here's the original solution from that site:

SELECT 
  a.id AS Start, 
  MIN( c.id ) AS End 
FROM tbl AS a
LEFT JOIN tbl AS b ON a.id = b.id + 1
LEFT JOIN tbl AS c ON a.id <= c.id
LEFT JOIN tbl AS d ON c.id = d.id - 1
WHERE b.id IS NULL 
  AND c.id IS NOT NULL
  AND d.id IS NULL
GROUP BY a.id; 

In order to make this work for dates, simply use:

SELECT 
  a.`date` AS Start, 
  MIN( c.`date` ) AS End 
FROM dates AS a
LEFT JOIN dates AS b ON a.`date` = DATE_ADD(b.`date`,INTERVAL 1 DAY)
LEFT JOIN dates AS c ON a.`date` <= c.`date`
LEFT JOIN dates AS d ON c.`date` = DATE_ADD(d.`date`,INTERVAL -1 DAY)
WHERE b.`date` IS NULL 
  AND c.`date` IS NOT NULL
  AND d.`date` IS NULL
GROUP BY a.`date`;