title: Joining next Sequential Row
body: <p>I am planing an SQL Statement right now and would need someone to look over my thougts.</p>
  
  <p>This is my Table:</p>
  
  <pre><code>id  stat    period
  --- ------- --------
  1   10      1/1/2008
  2   25      2/1/2008
  3    5      3/1/2008
  4   15      4/1/2008
  5   30      5/1/2008
  6    9      6/1/2008
  7   22      7/1/2008
  8   29      8/1/2008
  </code></pre>
  
  <p>Create Table</p>
  
  <pre><code>CREATE TABLE tbstats
    (
       id     INT IDENTITY(1, 1) PRIMARY KEY,
       stat   INT NOT NULL,
       period DATETIME NOT NULL
    )
  
  go
  
  INSERT INTO tbstats
              (stat,period)
  SELECT 10,CONVERT(DATETIME, '20080101')
  UNION ALL
  SELECT 25,CONVERT(DATETIME, '20080102')
  UNION ALL
  SELECT 5,CONVERT(DATETIME, '20080103')
  UNION ALL
  SELECT 15,CONVERT(DATETIME, '20080104')
  UNION ALL
  SELECT 30,CONVERT(DATETIME, '20080105')
  UNION ALL
  SELECT 9,CONVERT(DATETIME, '20080106')
  UNION ALL
  SELECT 22,CONVERT(DATETIME, '20080107')
  UNION ALL
  SELECT 29,CONVERT(DATETIME, '20080108')
  
  go 
  </code></pre>
  
  <p>I want to <strong>calculate</strong> the difference between each statistic and the next, and then calculate the mean value of the 'gaps.'</p>
  
  <p>Thougts:</p>
  
  <p>I need to join each record with it's subsequent row. I can do that using the ever flexible joining syntax, thanks to the fact that I know the id field is an integer sequence with no gaps.</p>
  
  <p>By aliasing the table I could incorporate it into the SQL query twice, then join them together in a staggered fashion by adding 1 to the id of the first aliased table. The first record in the table has an id of 1. 1 + 1 = 2 so it should join on the row with id of 2 in the second aliased table. And so on. </p>
  
  <p>Now I would simply subtract one from the other. </p>
  
  <p>Then I would use the ABS function to ensure that I always get positive integers as a result of the subtraction regardless of which side of the expression is the higher figure.</p>
  
  <p><strong>Is there an easier way to achieve what I want?</strong></p>