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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…