Find all rows where the start date is before the previous end date

Question

Is there a way to discard all records that overlap user-based dates?

For instance:

Table A 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 request that can pull ONLY the following record, because the start time to the previous time for the trainer (booked twice):

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

Answer 1

The EXIST code below should give you this answer. The code ensures that the start time of the merge record is before the start of the main list entry, while the collision start time is still after the start time of the message 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 using the 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 the code below to get the required string, however based on your logical string from the next trainer identifier (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 script disabled again)

or if you want to see all the lines except the erroneous one, use the code below

  ;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

First, you must sort by trainerId and Start_time. Then join the two tables with the 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 

Answer 5

As you use SQL Server 2012, you can use the LAG function, which is likely to be more efficient than self-join. The request becomes quite simple.

For each LAG line, you get an EndTime from the previous line (divided by TrainerID ). Then simply compare the StartTime with the current line with the EndTime from the previous line.

SQL Fiddle

 WITH CTE AS ( SELECT TrainerID ,StartTime ,EndTime ,LAG(EndTime) OVER(PARTITION BY TrainerID ORDER BY StartTime) AS PrevEndTime FROM TableA ) SELECT TrainerID ,StartTime ,EndTime FROM CTE WHERE StartTime < PrevEndTime ; 

results

 | TrainerID | StartTime | EndTime | |-----------|---------------------------|---------------------------| | 1234 | October, 01 2015 08:45:00 | October, 01 2015 09:15:00 |