title: sum Time difference on records of same date
body: <pre><code>WITH x AS 
  (
    SELECT *, 
      rn = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp)
    FROM AISDb
  where action = 'IN'
  ) ,
  y As
  (
    SELECT *, 
      rna = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp)
    FROM AISDb
  where action='OUT'
  ) 
  
  SELECT y.ID,x.ID,x.PersonName,y.PersonName,x.PersonRFID, DATEDIFF(MINUTE, x.TimeStamp, y.TimeStamp) 
  FROM x , y      
  where x.PersonRFID = y.PersonRFID
  And x.rn=y.rna
  AND  cast(x.Datestamp as date) = cast(y.Datestamp as date)
  Order By x.PersonName;
  </code></pre>
  
  <p>I get the below table when i execute the above query:</p>
  
  <pre><code>   Datestamp         personName                     ID             TimeDifference
  
      1/30/2013   Aman Ullah Khan     E2001026770D01432810029D            11
      1/30/2013   Aman Ullah Khan     E2001026770D01432810029D            0
      1/30/2013   Aman Ullah Khan     E2001026770D01432810029D            1
      1/30/2013   Amjad Ali Anjum     E2001026770D024125401476            0
      1/30/2013   Amjad Ali Anjum     E2001026770D024125401476            1
      2/9/2013    Amjad Ali Anjum     E2001026770D024125401476            31
      2/10/2013   Amjad Ali Anjum     E2001026770D024125401476            3
      2/10/2013   Amjad Ali Anjum     E2001026770D024125401476            0
      2/10/2013   Amjad Ali Anjum     E2001026770D024125401476            3 
      1/30/2013   Arif Shah           E2001026770D01852370206D            0
      3/13/2013   Asmat Ullah         E2001026770D007624101DA1          456
      3/20/2013   Asmat Ullah         E2001026770D007624101DA1          558
  </code></pre>
  
  <p>As u can see for e.g for person name "amanullah khan" there are 3 records of same date i.e. 1/30/2013, But i need a single record that sums 'time difference' if dates are same and i should get</p>
  
  <pre><code> 1/30/2013   Aman Ullah Khan     E2001026770D01432810029D            14
  </code></pre>