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

x
?
Solved

Datetime overlapping

Posted on 2004-11-15
4
Medium Priority
?
777 Views
Last Modified: 2008-03-17
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
Comment
Question by:pgrazide
  • 2
4 Comments
 
LVL 18

Expert Comment

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

Expert Comment

by:ShogunWade
ID: 12586234
try that
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
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)
 -- straddles
                                    or (a.startdate < b.startDate
                                          and a.enddate > b.enddate)
                                  )
                        )

0
 
LVL 8

Expert Comment

by:Jeff Tennessen
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))

Obviously, you'll have to make adjustments for your schema.

Jeff
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question