title: Only select records that do not start within the time frame of another record
body: <p>I'm trying to achieve the following goal using MS SQL Server 2005 but do not know how to do it.</p>
  
  <p>The goal is to select only records that do not start within the same time period as an anchor record.</p>
  
  <p>Rows that have same ID are a group and evaluated as part of that group.</p>
  
  <p>Start with the earliest date (A) based on StartDate, compare to the next row (B) that has the same ID.</p>
  
  <p>If B starts within A, mark B as invalid. Continue to compare A against all remaining records that have the same ID.  Mark any starting within A as invalid.</p>
  
  <p>Flag the next record that does not overlap with A as Valid.  Now repeat the same process as above (i.e. check to see if any subsequent records start within the time frame of the new valid record).</p>
  
  <p>Repeat this process until all records have been analyzed.</p>
  
  <p>Example:  Create the following table.</p>
  
  <pre><code>if object_id ('tempdb..#Dates') is not null drop table #Dates
  
  create table #Dates (ID int, StartDate datetime, EndDate datetime)
  Insert into #Dates
  Select 1, '7/23/2003'   ,   '8/22/2003'  union all
  select 1, '8/21/2003'   ,   '11/19/2003' union all
  select 1, '11/18/2003'  ,   '12/18/2003' union all
  select 1, '12/17/2003'  ,   '1/16/2004'  union all
  select 1, '1/15/2004'   ,   '2/14/2004'  union all
  select 1, '2/11/2004'   ,   '2/26/2004'  union all
  select 1, '9/14/2004'   ,   '10/14/2004' union all
  select 1, '10/5/2004'   ,   '10/20/2004' union all
  select 1, '11/20/2004'  ,   '12/20/2004' union all
  select 1, '12/19/2004'  ,   '1/18/2005'  union all
  select 1, '1/12/2005'   ,   '1/27/2005'  union all
  select 1, '2/27/2005'   ,   '3/11/2005'  
  </code></pre>
  
  <p>Expected output after applying the overlap logic rules:</p>
  
  <pre>
  ID  StartDate   EndDate     Valid
  --  ---------   ---------   -----
  1   7/23/2003   8/22/2003   1
  1   8/21/2003   11/19/2003  0
  1   11/18/2003  12/18/2003  1
  1   12/17/2003  1/16/2004   0
  1   1/15/2004   2/14/2004   1
  1   2/11/2004   2/26/2004   0
  1   9/14/2004   10/14/2004  1
  1   10/5/2004   10/20/2004  0
  1   11/20/2004  12/20/2004  1
  1   12/19/2004  1/18/2005   0
  1   1/12/2005   1/27/2005   1
  1   2/27/2005   3/11/2005   1
  </pre>