Kasinathan (10/13/2012)

---

Autoid Empid TimeIn Timeout
1 1009 13-10-2012 22:10 13-10-2012 22:15
2 1009 13-10-2012 22:20 13-10-2012 22:32
3 1009 13-10-2012 22:34 13-10-2012 22:36

I need to find the idel time for this.. like First row out time and secount row in time diff as 5 min like wise each row

CREATE TABLE #Temp 
(
   AutoId int,
   EmpId int,
   TimeIn datetime,
   TimeOut datetime
)
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (1, 1009, '10-13-2012 22:10', '10-13-2012 22:15');
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (2, 1009, '10-13-2012 22:20', '10-13-2012 22:32');
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (3, 1009, '10-13-2012 22:34', '10-13-2012 22:36');

-- Time difference per row --
SELECT AutoId,
   EmpId,
   TimeIn,
   TimeOut,
   DATEDIFF(mi, TimeIn, TimeOut) AS MinutesDifference
FROM #Temp

-- Time difference per employee --
SELECT EmpId,
   MIN(TimeIn) AS BeginTimeIn,
   MAX(TimeOut) AS EndTimeOut,
   DATEDIFF(mi, MIN(TimeIn), MAX(TimeOut)) AS MinutesDifference
FROM #Temp
GROUP BY EmpId

CREATE TABLE #Temp
(
AutoId int,
EmpId int,
TimeIn datetime,
TimeOut datetime
)
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (1, 1009, '10-13-2012 22:10', '10-13-2012 22:15');
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (2, 1009, '10-13-2012 22:20', '10-13-2012 22:32');
INSERT #Temp (AutoId, EmpId, TimeIn, TimeOut) VALUES (3, 1009, '10-13-2012 22:34', '10-13-2012 22:36');

SELECT * FROM #Temp

;WITH CTE AS
(
SELECT T.EmpId, T.TimeIn, T.TimeOut, ROW_NUMBER() OVER(ORDER BY T.TimeIn, T.TimeOut ) AS row_num
FROM #Temp AS T
)
SELECT N.EmpId, p.TimeIn, p.TIMEOUT, n.TimeIn, DATEDIFF(MI, p.TimeOut, n.TimeIn)
FROM CTE AS N
INNER JOIN
CTE AS P
ON P.row_num = N.row_num - 1;


DROP TABLE #Temp