sql - calculate the difference of the time between In and out

Question

I have one table name checkinout in which all the record of employee punch is recorded.

look like:

  EMP_id        time         in/out
  --------  ---------------  -------
  11        8/19/2013 08:19    I    
  11        8/19/2013 12:37    O    
  11        8/19/2013 13:29    I    
  11        8/19/2013 14:19    O    
  11        8/19/2013 16:48    I    
  11        8/19/2013 18:44    O    

I want to calculate the difference in time of each I and O (one by one)

eg: First I =08:19

First O=:12:37

so 08:19-12:37=difference in time

so on for second and third.

Answer 1

If you're using SQL Server 2012 then use the LAG function like this:

SELECT *, 
       DATEDIFF(HH, TIME, LAG(TIME) 
                            OVER ( 
                              ORDER BY TIME)) 
FROM   TABLE1 

A working example for this can be found on SQL Fiddle

---

If you are using SQL Server 2008 then you'll have to do an self join like this:

SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           ORDER BY TIME)RN 
INTO   #TEMP1 
FROM   TABLE1 

SELECT t1.*, 
       DATEDIFF(HH, t1.TIME, t2.TIME) 
FROM   #TEMP1 t1 
       LEFT JOIN #TEMP1 t2 
              ON t1.RN = t2.RN - 1 

A working example for this can be found on SQL Fiddle.

---

EDIT
If you want to display the difference in a hh:mm:ss format try something like this:

SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           ORDER BY TIME)RN 
INTO   #TEMP1 
FROM   TABLE1 

SELECT T.EMP_ID, 
       T.TIME, 
       T.[IN/OUT], 
       CASE WHEN DIFF/3600 <10 THEN '0' ELSE '' END + CAST(DIFF/3600 AS VARCHAR( 
       2)) + 
       ':' 
       + CASE WHEN DIFF%3600/60 < 10 THEN '0' ELSE '' END + CAST(DIFF%3600/60 AS 
       VARCHAR(2)) 
       + ':00' minutes 
FROM   (SELECT t1.*, 
               DATEDIFF(SECOND, t1.TIME, t2.TIME) diff 
        FROM   #TEMP1 t1 
               LEFT JOIN #TEMP1 t2 
                      ON t1.RN = t2.RN - 1)T 

And here is the third version on SQL Fiddle