title: Combine rows when the end time of one is the start time of another
body: <p>I am having difficulty writing a query.  I need to combine rows of time-consecutive states into a single state.  For example, given the data:</p>
  
  <pre><code>start                    end                      state   seconds
  2011-04-21 08:13:30.000  2011-04-21 08:18:00.000  STATE1  270
  2011-04-21 08:18:00.000  2011-04-21 08:22:30.000  STATE1  270
  2011-04-21 08:22:30.000  2011-04-21 08:26:26.000  STATE1  236
  2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
  2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
  2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
  2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30
  </code></pre>
  
  <p>I need to combine rows only when <code>row2.state = row1.state AND row2.start = row1.end</code> and come up with an overall start and end time of the state.  The result should be:</p>
  
  <pre><code>start                    end                      state   seconds
  2011-04-21 08:13:30.000  2011-04-21 08:26:26.000  STATE1  776
  2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
  2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
  2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
  2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30
  </code></pre>
  
  <p>Here is the example SQL:</p>
  
  <pre><code>CREATE TABLE Data (
      ID int IDENTITY(1,1) not null,
      Date DateTime not null,
      State nvarchar(40) not null,
      Seconds int not null,
      PRIMARY KEY(ID)
  );
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:13:30.000', 'STATE1', 270)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:18:00.000', 'STATE1', 270)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:22:30.000', 'STATE1', 236)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:26.000', 'STATE2', 21)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:47.000', 'STATE3', 43)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:27:30.000', 'STATE1', 50)
  INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:40:30.000', 'STATE1', 30)
  
  SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data
  </code></pre>
  
  <p>Thanks in advance!</p>