title: mysql select rows by consecutive date
body: <p>I have a table of available date blocks (7 days in my case) which may or may not be consecutive:</p>
  
  <pre><code>start_date    end_date    booked    id    room_id
  
  2012-07-14    2012-07-21    0       1        6
  2012-07-21    2012-07-28    0       2        6
  2012-07-28    2012-08-04    1       3        6
  2012-08-04    2012-08-11    0       4        6
  </code></pre>
  
  <p>What I'd like to do is be able to get a result set that gives me one row per X weeks of consecutive unbooked dates, within a date range.</p>
  
  <p>So, for 2 week blocks starting on the 14th of July and using the above table data, I would expect the following:</p>
  
  <pre><code>start_date    end_date    booked
  2012-07-14    2012-07-28    0
  </code></pre>
  
  <p>The second block of 2 weeks would not be returned as one of the component weeks is booked.</p>
  
  <p>Here are a few ideas I've tried already:</p>
  
  <pre><code>SELECT 
      MIN(start_date) AS start_date_min,
      MAX(end_date) AS end_date_max,
      CAST(GROUP_CONCAT(id) AS CHAR) AS ids,
      SUM(booked) AS booked
  FROM 
      available_dates
  WHERE
      (start_date&gt;=20120714 AND end_date&lt;=DATE_ADD(20120714, INTERVAL 14 DAY))
  GROUP BY
      room_id
  HAVING
      end_date_max=DATE_ADD(20120714, INTERVAL 14 DAY)
  </code></pre>
  
  <p>This gets me part of the way, however doesn't get me the <em>consecutive</em> results - that is the important part. It also only returns a single result (probably because of the HAVING clause) when I widen the test data.</p>
  
  <p>Can anyone point me in the right direction?</p>