Datetime overlapping

Posted on 2004-11-15
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!
Question by:pgrazide
    LVL 18

    Expert Comment

    SELECT * FROM Table1 a WHERE EXISTS (SELECT 1 FROM Table1 WHERE a.StartDate BETWEEN StartDate AND EndDate)
    LVL 18

    Expert Comment

    try that
    LVL 50

    Accepted Solution

    select A.Name, A.StartDate,A.endDate
      from ConfTable as A
     Where exists (Select 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)

    LVL 7

    Expert Comment

    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,

    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.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now