Morning,

Apologies if there is a rule of somekind that states not to reopen old posts, this is my first time posting so be gentle 😉

I found this post extremely useful afer trawling the internet for a solution to my problem

Although I'm 90% there I still have a problem and hopefully someone can help.

I have an additional few columns in the mix, but for example purposes let's just say 1 column which is employee.

So effecively I have multiple employees with overlapping times, I need the times merged (as above) but for each employee.

Here's the DDL:

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

I've tried running:

Select * from Overlapping

SELECT

s1.EmployeeId,

s1.LogInTime,

MIN(t1.LogOutTime) AS LogOutTime

FROM Overlapping s1

INNER JOIN Overlapping t1 ON s1.LogInTime <= t1.LogOutTime

AND NOT EXISTS(SELECT * FROM Overlapping t2

WHERE t1.LogOutTime >= t2.LogInTime AND t1.LogOutTime < t2.LogOutTime)

WHERE NOT EXISTS(SELECT * FROM Overlapping s2

WHERE s1.LogInTime > s2.LogInTime AND s1.LogInTime <= s2.LogOutTime)

GROUP BY s1.EmployeeId,s1.LogInTime

ORDER BY s1.LogInTime

But I dont get the results I expect.

If I only insert the records for employee 501 in the table (for example) and run it it works a treat.

Please help 🙂

Thanks

Steve