Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Does anyone have a good query for detecting schedule conflicts?

    Hello, first post here.

    I have a database of schedule data, and I need a query to detect conflicts. If event A starts at 10/1/09 3:00 and ends at 10/1/09 13:00; and event B starts at 10/1/09 12:00 and ends at 10/1/09 15:00 then the two are in conflict. I have searched all over to find a query to detect this kind of thing and come up empty. Anyone got any ideas?

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post your CREATE TABLE statement, some sample data (in the form of INSERT statements) and I'm sure we can help
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    OK, here goes.

    CREATE TABLE Tasks ([ID] int IDENTITY(1,1) NOT NULL,
    TaskName varchar(255) NULL,
    StartDate smalldatetime NULL,
    FinishDate small datetime NULL)

    INSERT INTO Tasks (TaskName, StartDate, FinishDate )
    VALUES ('Task 1', '10/1/09 03:00', '10/1/09 13:00')

    INSERT INTO Tasks (TaskName, StartDate, FinishDate )
    VALUES ('Task 2', '10/1/09 04:00', '10/1/09 05:00')

    INSERT INTO Tasks (TaskName, StartDate, FinishDate )
    VALUES ('Task 3', '10/1/09 12:00', '10/1/09 15:00')

    INSERT INTO Tasks (TaskName, StartDate, FinishDate )
    VALUES ('Task 4', '10/1/09 16:00', '10/1/09 23:00')

    That should give you 4 tasks, three of which are in conflict, ie. Task 2 starts and finishes during the time Task 1 happens, and Task 3 starts before task 1 ends, but finishes after Task 1. I need a query that will give me the three tasks that are in conflict. Does that make sense?

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SELECT DISTINCT t1.*
    FROM Tasks t1, Tasks t2
    WHERE t1.StartDate < t2.FinishDate
    AND t1.FinishDate > t2.StartDate
    AND t1.ID <> t2.ID ;

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    OK, I tried that on my actual database, and got a huge number of items. Now how do I narrow that down to a certain timeperiod? Say for the month of October or the first day of October?

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Something like this:

    SELECT DISTINCT t1.*
    FROM Tasks t1, Tasks t2
    WHERE t1.StartDate < t2.FinishDate
    AND t1.FinishDate > t2.StartDate
    AND t1.ID <> t2.ID
    AND t2.StartDate < '20091101'
    AND t2.FinishDate >= '20091001' ;

  7. #7
    Join Date
    Sep 2009
    Posts
    4
    OK, that seems to work, Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •