Date diff between timein and timeout rows

Question

Date diff between timein and timeout rows

Kasinathan

SSC-Addicted

Points: 426
More actions
October 13, 2012 at 11:39 am

#269505

AutoidEmpidTimeIn Timeout
1100913-10-2012 22:10 13-10-2012 22:15
2100913-10-2012 22:20 13-10-2012 22:32
3100913-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
Please help me
thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

Kasinathan (10/13/2012)
AutoidEmpidTimeIn Timeout
1100913-10-2012 22:10 13-10-2012 22:15
2100913-10-2012 22:20 13-10-2012 22:32
3100913-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

Are you looking for the time difference in each record? or for each employee? I'm not sure what you're saying about 5 minutes.

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

HTH,

Rob

sql-programmers SSC-Addicted Points: 494 More actions · Answer 2

Hi,

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

SQL Sever Performance tuning