[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Datetime overlapping

Posted on 2004-11-15
Medium Priority
777 Views
I'm trying to determine the best way to approach this problem.  I have a series of conferences and I need to determine what conferences overlap on date and time.

Conference One  11/20/04 - 11/23/04  9:00AM - 4:00PM
Conference Two 11/21/04 - 11/22/04  9:00AM - 3:00PM
Conference Three 11/22/04 - 11/23/04  9:00AM - 5:00PM
Conference Four  11/20/04 - 11/23/04  9:00AM - 4:00PM

How do I determine my overlapping conferences?

Thanks for you help!
Paul
0
Question by:pgrazide
• 2

LVL 18

Expert Comment

ID: 12586226
SELECT * FROM Table1 a WHERE EXISTS (SELECT 1 FROM Table1 WHERE a.StartDate BETWEEN StartDate AND EndDate)
0

LVL 18

Expert Comment

ID: 12586234
try that
0

LVL 50

Accepted Solution

ID: 12586275
select A.Name, A.StartDate,A.endDate
from ConfTable as A
Where exists (Select b.name from confTable as B
where A.Name > B.Name
and  (
-- Starts before
(A.StartDate <= b.startDate
and A.EndDate >= b.startDate)
-- starts after and Ends before
or (A.endDate >= b.StartDate
and A.startDate <= b.enddate)
or (a.startdate < b.startDate
and a.enddate > b.enddate)
)
)

0

LVL 8

Expert Comment

ID: 12586423
Here's another possibility:

SELECT c2.Conference + ' overlaps ' + c1.Conference
FROM Conferences c1
INNER JOIN Conferences c2
ON c1.ConferenceID <> c2.ConferenceID
AND c1.StartDate < c2.StartDate
AND c1.EndDate > c2.StartDate
ORDER BY c2.ConferenceID,
c1.ConferenceID

Which, with the data you posted, would give this result:

Conference Two overlaps Conference One
Conference Two overlaps Conference Four
Conference Three overlaps Conference One
Conference Three overlaps Conference Two
Conference Three overlaps Conference Four

Since I didn't know how your conference data was stored, I used this table structure:

CREATE TABLE Conferences
(ConferenceID int NOT NULL,
Conference varchar (50),
StartDate datetime NULL,
EndDate datetime NULL,
CONSTRAINT PK_Conferences PRIMARY KEY  CLUSTERED (ConferenceID))

Jeff
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month19 days, 14 hours left to enroll