title: How to find date ranges in records with consecutive dates?
body: <p>I have a table with data like so:</p>
  
  <pre><code>ID    StartDate    EndDate     EffectiveDate
  --    ---------    -------     -------------
  1     1/1/2009     12/31/2009    12/31/2009
  1     7/1/2009     12/31/2009    7/1/2009
  1     8/1/2009     12/31/2009    8/1/2009
  2     1/1/2010     12/31/2010    12/31/2010
  2     3/1/2010     12/31/2010    12/31/2010
  </code></pre>
  
  <p>I need a query that will format it like this, however:</p>
  
  <pre><code>ID    StartDate    EndDate     EffectiveDate
  --    ---------    -------     -------------
  1     1/1/2009     6/30/2009    null
  1     7/1/2009     7/31/2009    7/1/2009
  1     8/1/2009     12/31/2009   8/1/2009
  2     1/1/2010     2/28/2010    null
  2     3/1/2010     12/31/2010   12/31/2010
  ...
  </code></pre>
  
  <p>It's basically like a timeline with segmented "points", and each point is a new StartDate with an EndDate of the next point, and so on.</p>
  
  <p>I've tried using a CTE, as well as the following query:</p>
  
  <pre><code>   SELECT t1.RfqItemOptionId, 
            t1.StartDate, 
            MIN(t2.EffectiveDate) EndDate,  
            t1.EffectiveDate EffectiveDate
       FROM @OptionPeriods t1 
  LEFT JOIN @OptionPeriods t2 ON t1.RfqItemOptionId = t2.RfqItemOptionId
                             AND t1.EffectiveDate &lt; t2.EffectiveDate
   GROUP BY t1.RfqItemOptionId, t1.StartDate, t1.EffectiveDate
  </code></pre>
  
  <p>Which is close...but no cigar :(</p>
  
  <p>Can anyone help?</p>