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

Combine overlapping datetime to return single overlapping range record Expand / Collapse
Author
Message
Posted Monday, November 30, 2009 12:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:41 PM
Points: 45, Visits: 289
For e.g. -- I have these records in a table.

Eg.

Table: Overlapping Days

Has following data –

ID StartDate EndDate
-----------------------------------------------------------------
1 01-01-2009 10:00:00 01-01-2009 10:40:00
2 01-01-2009 12:00:00 01-01-2009 14:00:00
3 01-01-2009 12:30:00 01-01-2009 15:00:00
4 01-01-2009 19:00:00 01-01-2009 19:30:00
5 01-01-2009 20:05:00 01-01-2009 20:25:00
6 01-01-2009 19:45:00 01-01-2009 20:20:00


Result required:

StartDate EndDate
-------------------------------------------
01-01-2009 10:00:00 01-01-2009 10:40:00
01-01-2009 12:00:00 01-01-2009 15:00:00
01-01-2009 19:00:00 01-01-2009 19:30:00
01-01-2009 19:45:00 01-01-2009 20:25:00

· We need to club the overlapping events and return rows accordingly.
· There could be more combinations..of Startdate and enddate in the Source

I am able to find out rows that are overlapping but I need to group these into sets and find Min(StartDate) and Max(EndDate) for each such set.


Sample Table- DDL
------------------------

CREATE TABLE [Overlapping](

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

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL

) ON [PRIMARY]

GO


SET IDENTITY_INSERT [Overlapping] ON

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (1, CAST(0x00009B8400B54640 AS DateTime), CAST(0x00009B8400BD83A0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (2, CAST(0x00009B8400000000 AS DateTime), CAST(0x00009B8400107AC0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (3, CAST(0x00009B8400A4CB80 AS DateTime), CAST(0x00009B8400B964F0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (4, CAST(0x00009B84009450C0 AS DateTime), CAST(0x00009B840099CF00 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (5, CAST(0x00009B840099CF00 AS DateTime), CAST(0x00009B84009DEDB0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (6, CAST(0x00009B84009C8E20 AS DateTime), CAST(0x00009B8400A0ACD0 AS DateTime))

SET IDENTITY_INSERT [Overlapping] OFF
Post #826031
Posted Monday, November 30, 2009 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
Please provide sample data as described in the first link in my signature together with what you've tried so far.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #826060
Posted Monday, November 30, 2009 2:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:41 PM
Points: 45, Visits: 289
Hi Lutz,

Sample table and data -
CREATE TABLE [Overlapping](

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

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL

) ON [PRIMARY]

GO


SET IDENTITY_INSERT [Overlapping] ON

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (1, CAST(0x00009B8400B54640 AS DateTime), CAST(0x00009B8400BD83A0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (2, CAST(0x00009B8400000000 AS DateTime), CAST(0x00009B8400107AC0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (3, CAST(0x00009B8400A4CB80 AS DateTime), CAST(0x00009B8400B964F0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (4, CAST(0x00009B84009450C0 AS DateTime), CAST(0x00009B840099CF00 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (5, CAST(0x00009B840099CF00 AS DateTime), CAST(0x00009B84009DEDB0 AS DateTime))

INSERT [Overlapping] ([DKey], [StartDate], [EndDate]) VALUES (6, CAST(0x00009B84009C8E20 AS DateTime), CAST(0x00009B8400A0ACD0 AS DateTime))

SET IDENTITY_INSERT [Overlapping] OFF




What I have tried so far -
select
A.DKey,
MIN(A.StartDate) StartD,
MAX(B.EndDate) EndD
from Overlapping A
cross join Overlapping B
where B.StartDate between A.StartDate and A.EndDate
and (A.StartDate <> B.StartDate and A.EndDate <> B.EndDate )
group by A.DKey
order by StartD,EndD

This is not complete and it doesnt work for a scenario wherein there are more than two overlapping events. Writing this down just to let you know the direction of my thouights on this.
Post #826065
Posted Monday, November 30, 2009 3:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

Try this

SELECT s1.StartDate, 
MIN(t1.EndDate) AS EndDate
FROM Overlapping s1
INNER JOIN Overlapping t1 ON s1.StartDate <= t1.EndDate
AND NOT EXISTS(SELECT * FROM Overlapping t2
WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate)
WHERE NOT EXISTS(SELECT * FROM Overlapping s2
WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate)
GROUP BY s1.StartDate
ORDER BY s1.StartDate



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #826094
Posted Monday, November 30, 2009 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:41 PM
Points: 45, Visits: 289
Thank you very much. This works fine. :)
Post #826110
Posted Wednesday, December 12, 2012 1:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:32 AM
Points: 5, Visits: 41
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





Post #1395496
Posted Wednesday, December 12, 2012 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:32 AM
Points: 5, Visits: 41
Sorry,... just to be clear on what I would expect, if I run for 1 employee this is what I get:

Load Table

8 501 07:13:13 07:18:04
9 501 07:13:13 07:13:26
10 501 08:35:56 08:36:20
11 501 08:35:56 09:00:00
12 501 14:08:08 18:30:26
12 501 15:45:25 18:30:32

Combined Result

501 07:13:13 07:18:04
501 08:35:56 09:00:00
501 14:08:08 18:30:32

When run for more than 1 employee these are the results:

DKey EmployeeId LoginTime LogOutTime
1 500 07:47:19 08:03:05
2 500 07:47:19 09:30:06
3 500 07:47:19 19:55:32
4 500 09:47:08 11:30:13
5 500 11:45:56 13:14:51
6 500 13:47:38 14:33:32
7 500 15:45:49 19:55:27
8 501 07:13:13 07:18:04
9 501 07:13:13 07:13:26
10 501 08:35:56 08:36:20
11 501 08:35:56 09:00:00
12 501 14:08:08 18:30:26
12 501 15:45:25 18:30:32

Result set

EmployeeId LogInTime LogOutTime
501 07:13:13 07:18:04
500 07:47:19 19:55:32

It doesnt group the data correctly

Thanks

Steve
Post #1395515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse