Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date diff between timein and timeout rows Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 29, 2013 2:14 AM
Points: 13, Visits: 46
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

Please help me

thanks in advance
Post #1372442
Posted Saturday, October 13, 2012 12:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 1,166, Visits: 1,192
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

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
Post #1372453
Posted Monday, October 15, 2012 4:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 11:02 PM
Points: 18, Visits: 274
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
Post #1372646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse