title: Trying to compare two consecutive records
body: <p>I am using SQL Server 2008 and I need help in writing a query that compares two consecutive records.</p>
  
  <pre><code>select recordDate 
  from SiteHistory 
  where siteId = 556145 
    and isRecent = 0 
    and isRunning = 1
  order by 
      recordDate DESC
  </code></pre>
  
  <p>Gives me around 2000 rows that looks like this:</p>
  
  <pre><code>recordDate
  -----------------------
  2013-05-08 20:04:23.357
  2013-05-08 19:45:26.417
  2013-05-08 19:30:24.810 
  2013-05-08 19:17:22.843
  2013-05-08 19:00:16.017
  2013-05-08 18:44:14.230
  .....
  .....
  </code></pre>
  
  <p>Now I need to compare the date of each row with the next row and count how many times the difference between two consecutive dates is greater than 15mins.
  This is what I could come up with so far:</p>
  
  <pre><code>;with temp as(
  select row_number()over(order by recordDate DESC)as 'row', recordDate 
  from SiteHistory 
  where siteId = 556145 and isRecent =0 and isRunning=1
  )
  
  select COUNT(*) as Count from temp t1
  INNER JOIN temp t2 ON t2.row = t1.row+1
  where DATEDIFF(mm,t1.recordDate,t2.recordDate)&gt;15
  </code></pre>
  
  <p>However, this doesn't give me the desired. Please let me know how I can correct this to suit my requirements.</p>