title: Combine consecutive date ranges
body: <p>Using SQL Server 2008 R2,</p>
  
  <p>I'm trying to combine date ranges into the maximum date range given that one end date is next to the following start date. </p>
  
  <p>The data is about different employments. Some employees may have ended their employment and have rejoined at a later time. Those should count as two different employments (example ID 5). Some people have different types of employment, running after each other (enddate and startdate neck-to-neck), in this case it should be considered as one employment in total (example ID 30). </p>
  
  <p>An employment period that has not ended has an enddate that is null.</p>
  
  <p>Some examples is probably enlightening:</p>
  
  <pre><code>declare @t as table  (employmentid int, startdate datetime, enddate datetime)
  
  insert into @t values
  (5, '2007-12-03', '2011-08-26'),
  (5, '2013-05-02', null),
  (30, '2006-10-02', '2011-01-16'),
  (30, '2011-01-17', '2012-08-12'),
  (30, '2012-08-13', null),
  (66, '2007-09-24', null)
  
  -- expected outcome
  EmploymentId StartDate   EndDate
  5            2007-12-03  2011-08-26
  5            2013-05-02  NULL
  30           2006-10-02  NULL
  66           2007-09-24  NULL
  </code></pre>
  
  <p>I've been trying different "islands-and-gaps" techniques but haven't been able to crack this one. </p>