title: SQL Server find datediff between different rows, sum
body: <p>I am trying to build a query that analyzes data in our time tracking system. Every time a user swipes in or out, it makes a row recording the swipe time and On or Off site (entry or exit). In user 'Joe Bloggs' case there are 4 rows, which I want to pair and calculate a total time spent on site for Joe Bloggs.</p>
  
  <p>The problem is that there are records that are not as easy to pair.  In the example given, the second user has two  consecutive 'on's, and I need to find a method for ignoring repeated 'on' or 'off' rows. </p>
  
  <pre><code>ID  | Time                    |OnOffSite| UserName   
  ------------------------------------------------------
  123 | 2011-10-25 09:00:00.000 | on      | Bloggs Joe |
  124 | 2011-10-25 12:00:00.000 | off     | Bloggs Joe |
  125 | 2011-10-25 13:00:00.000 | on      | Bloggs Joe |
  126 | 2011-10-25 17:00:00.000 | off     | Bloggs Joe |
  127 | 2011-10-25 09:00:00.000 | on      | Jonesy Ian |
  128 | 2011-10-25 10:00:00.000 | on      | Jonesy Ian |
  129 | 2011-10-25 11:00:00.000 | off     | Jonesy Ian |
  130 | 2011-10-25 12:00:00.000 | on      | Jonesy Ian |
  131 | 2011-10-25 15:00:00.000 | off     | Jonesy Ian |
  </code></pre>
  
  <p>My System is MS SQL 2005. The reporting period for the query is Monthly.</p>
  
  <p>Can anyone suggest a solution?  my data is already grouped in a table by Username and time, with the ID field being Identity.</p>