Find all rows where start date is before a prior end date

Question

Is there a way to pull back all records that have overlapping datetimes based on a user?

For instance;

TableA has the following rows;

TrainerID            StartTime            EndTime
1234                 10-1-2015 08:30      10-1-2015 09:00 
1234                 10-1-2015 08:45      10-1-2015 09:15
1234                 10-1-2015 09:30      10-1-2015 10:00
2345                 10-1-2015 08:45      10-1-2015 09:15
2345                 10-1-2015 09:30      10-1-2015 10:00

I need a query that can pull ONLY the following record because it's start time is before the previous end time for the trainer (double booked):

1234                 10-1-2015 08:45      10-1-2015 09:15

Answer 1

The EXIST code below should give you that answer. The code ensures that the start time of the clashing entry is before the start of the main list entry while the start time of the clash is still after the start time of the mail list entry.

SELECT * 
FROM tblTest clashing
WHERE EXISTS 
(
    SELECT 1
    FROM tblTest mainlist
    WHERE clashing.trainderid = mainlist.trainderid 
        AND clashing.starttime < mainlist.endtime
        AND clashing.starttime > mainlist.starttime
)

This can also be written with an IN statement, but EXIST is much more efficient

Answer 2

To remove overlapping dates you can use:

Demo

CREATE TABLE #TABLEA( TrainerID INT, StartDate DATETIME, EndDate DATETIME);
  
INSERT INTO #TABLEA
SELECT 1234,  '10-1-2015 08:30', '10-1-2015 09:00'
UNION ALL SELECT 1234 , '10-1-2015 08:45',    '10-1-2015 09:15'
UNION ALL SELECT 1234 , '10-1-2015 09:30',    '10-1-2015 10:00'
UNION ALL SELECT 2345 , '10-1-2015 08:45',    '10-1-2015 09:15'
UNION ALL SELECT 2345 , '10-1-2015 09:30',    '10-1-2015 10:00';

SELECT
  D.TrainerID,
  [StartTime] = D.StartDate,
  [EndTime] = (SELECT MIN(E.EndDate)
               FROM #TABLEA E
               WHERE E.EndDate >= D.EndDate
                 AND E.TrainerID = D.TrainerID
                 AND NOT EXISTS (SELECT 1
                                 FROM #TABLEA E2
                                 WHERE E.StartDate < E2.StartDate
                                   AND E.EndDate > E2.StartDate
                                   AND E.TrainerID = E2.TrainerID)) 
FROM #TABLEA D
WHERE NOT EXISTS ( SELECT 1
                   FROM #TABLEA D2
                   WHERE D.StartDate < D2.EndDate
                     AND D.EndDate > D2.EndDate
                     AND D.TrainerID = D2.TrainerID);

Answer 3

You can use below code to get the required row, however based on your logic row from next trainer id (i.e. 2345) will also be qualified

  DECLARE @Trainers TABLE
(
    TrainerId INT,
    Start_Time datetime,
    End_Time datetime
)
INSERT INTO @Trainers VALUES 
(1234,'10-1-2015 08:30','10-1-2015 09:00 '),
(1234,'10-1-2015 08:45','10-1-2015 09:15'),
(1234,'10-1-2015 09:30','10-1-2015 10:00'),
(2345 ,' 10-1-2015 08:45','10-1-2015 09:15'),
(2345 ,' 10-1-2015 09:30 ',' 10-1-2015 10:00')


;WITH TrainersTemp AS
        (
        SELECT  *, ROW_NUMBER() OVER ( ORDER BY trainerid) AS rn
        FROM    @Trainers
        )
SELECT  CX.TrainerId, CX.Start_Time, CX.End_Time
FROM    TrainersTemp CX JOIN TrainersTemp CY
ON      CX.rn = CY.rn + 1
WHERE CY.End_Time < CX.Start_Time

Demo (SQL fiddle is down again)

or if you want to see all rows except the faulty one then use below code

 ;WITH TrainersTempAll AS
        (
        SELECT  *, ROW_NUMBER() OVER ( ORDER BY trainerid) AS rn
        FROM    @Trainers
        )
SELECT  CX.TrainerId, CX.Start_Time, CX.End_Time
FROM    TrainersTempAll CX JOIN TrainersTempAll CY
ON      CX.rn = CY.rn + 1

Answer 4

Firstly you should sort by trainerId and Start_time. And then join two tables with correct condition.

Try this query:

;WITH TrainersTemp AS
(
    SELECT  *, ROW_NUMBER() OVER ( ORDER BY trainerid, Start_Time) AS row_num
    FROM    Trainers
)
select t2.* from TrainersTemp t1 
join TrainersTemp t2 on t1.TrainerId = t2.TrainerId and t1.row_num = t2.row_num-1
where t2.Start_Time<t1.End_Time