title: Aggregate adjacent only records with T-SQL
body: <p>I have (simplified for the example) a table with the following data</p>
  
  <pre><code>Row Start       Finish       ID  Amount
  --- ---------   ----------   --  ------
    1 2008-10-01  2008-10-02   01      10
    2 2008-10-02  2008-10-03   02      20
    3 2008-10-03  2008-10-04   01      38
    4 2008-10-04  2008-10-05   01      23
    5 2008-10-05  2008-10-06   03      14
    6 2008-10-06  2008-10-07   02       3
    7 2008-10-07  2008-10-08   02       8
    8 2008-10-08  2008-11-08   03      19
  </code></pre>
  
  <p>The dates represent a period in time, the ID is the state a system was in during that period and the amount is a value related to that state.</p>
  
  <p>What I want to do is to aggregate the Amounts for <em>adjacent</em> rows with the <em>same</em> ID number, but keep the same overall sequence so that contiguous runs can be combined.  Thus I want to end up with data like:</p>
  
  <pre><code>Row Start       Finish       ID  Amount
  --- ---------   ----------   --  ------
    1 2008-10-01  2008-10-02   01      10
    2 2008-10-02  2008-10-03   02      20
    3 2008-10-03  2008-10-05   01      61
    4 2008-10-05  2008-10-06   03      14
    5 2008-10-06  2008-10-08   02      11
    6 2008-10-08  2008-11-08   03      19
  </code></pre>
  
  <p>I am after a T-SQL solution that can be put into a SP, however I can't see how to do that with simple queries.  I suspect that it may require iteration of some sort but I don't want to go down that path.</p>
  
  <p>The reason I want to do this aggregation is that the next step in the process is to do a SUM() and Count() grouped by the unique ID's that occur within the sequence, so that my final data will look something like:</p>
  
  <pre><code>ID  Counts Total
  --  ------ -----
  01       2    71
  02       2    31
  03       2    33
  </code></pre>
  
  <p>However if I do a simple </p>
  
  <pre><code>SELECT COUNT(ID), SUM(Amount) FROM data GROUP BY ID
  </code></pre>
  
  <p>On the original table I get something like</p>
  
  <pre><code>ID  Counts Total
  --  ------ -----
  01       3    71
  02       3    31
  03       2    33
  </code></pre>
  
  <p>Which is not what I want.</p>