Wow thanks Dwain (and Itzik Ben-Gan) worked an absoulate treat 🙂

Just to demontrate that it worked with multiple employees I've slightly tweaked it but all in all fantastic, I shall save that forever.

Thanks

Steve

CREATE TABLE [Overlapping](

[DKey] [int] IDENTITY(1,1) NOT NULL,

[EmployeeId] [int] NULL,

[LoginTime] [varchar](8) NULL,

[LogOutTime] [varchar](8) NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [Overlapping] ON

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (1, 500 ,'07:47:19', '08:03:05')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (2, 500 ,'07:47:19','09:30:06')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (3, 500 ,'07:47:19','19:55:32')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (4, 500 ,'09:47:08', '11:30:13')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (5, 500 ,'11:45:56','13:14:51')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (6, 500 ,'13:47:38', '14:33:32')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (7, 500 ,'15:45:49', '19:55:27')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (8, 501 , '07:13:13','07:18:04')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (9, 501 , '07:13:13','07:13:26')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (10, 501 , '08:35:56','08:36:20')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (11, 501 , '08:35:56','09:00:00')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (12, 501 , '14:08:08','18:30:26')

INSERT [Overlapping] ([DKey], [EmployeeId], [LoginTime], [LogOutTime]) VALUES (12, 501 , '15:45:25','18:30:32')

SET IDENTITY_INSERT [Overlapping] OFF

Select * from overlapping

-- 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 overlapping

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 C3

GROUP BY EmployeeId, grpnm

Drop table Overlapping