DECLARE @T TABLE (DKey INT, EmployeeId INT, LoginTime TIME, LogOutTime TIME)INSERT INTO @TSELECT 8,501,'07:13:13','07:18:04'UNION ALL SELECT 9,501,'07:13:13','07:13:26'UNION ALL SELECT 10,501,'08:35:56','08:36:20'UNION ALL SELECT 11,501,'08:35:56','09:00:00'UNION ALL SELECT 12,501,'14:08:08','18:30:26'UNION ALL SELECT 12,501,'15:45:25','18:30:32'SELECT *FROM @T-- Method by Itzik Ben-Gan-- http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx ;WITH C1 AS ( SELECT EmployeeId, ts, Type ,e=CASE Type WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY EmployeeId, Type ORDER BY LogoutTime) END ,s=CASE Type WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY EmployeeId, Type ORDER BY LoginTime) END FROM @T CROSS APPLY ( VALUES (1, LoginTime), (-1, LogoutTime)) a(Type, ts) ), C2 AS ( SELECT C1.* ,se=ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY ts, Type DESC) FROM C1), C3 AS ( SELECT EmployeeId, ts ,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY ts)-1) / 2 + 1) FROM C2 WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)SELECT EmployeeId, StartDate=MIN(ts), EndDate=MAX(ts)FROM C3GROUP BY EmployeeId, grpnm
SELECT EmployeeId, MIN(LoginTime) AS LoginTime, MAX(LogOutTime) AS LogOutTimeFROM (SELECT DENSE_RANK() OVER(PARTITION BY base.EmployeeId ORDER BY base.LoginTime), base.EmployeeId, base.LoginTime, overLap.LogOutTime FROM Overlapping base INNER JOIN Overlapping overLap ON base.EmployeeId = overLap.EmployeeId WHERE (overLap.LoginTime > Base.LoginTime AND overLap.LoginTime < base.LogOutTime) OR (overLap.LoginTime = base.LoginTime AND overLap.LogOutTime > base.LogOutTime) AND base.DKey <> overLap.DKey )a(pos,EmployeeId,LoginTime,LogOutTime)GROUP BY pos, EmployeeId;