Search code examples
sqlhana

How to find the overlapping records in a Trips made from a Driver using Start and End Date Time


I have a Trip data set as below where I have Trips for a given drivers and its associated vehicles and its Start and End Date Time.

Data Set enter image description here

From the above, I have Trips where there is a overlap between Start Date Time and End Date Time between its records.

Processed Data Set enter image description here

That implies a Driver traveled or made a trip more than once at any given time which is not possible as a single driver can drive one vehicle at the given period.

So, I would like to extract them from the data set leaving behind the non-overlapping or the correct ones as below.

Final Requirement enter image description here

Could you please suggest the SQL for the above requirement.

Best Regards,


Solution

  • I used a temporary table cte to collect all tripID that are overlapping per driver. Then I will use these tripID to join to the original table. See below and sample here: http://sqlfiddle.com/#!18/236dd/3

    with cte as ( 
        select t1.tripID as ID1, 
               t2.tripID as ID2
        from yourTable t1
        join yourTable t2 
        on t1.driverid=t2.driverid
        where t1.startdatetime between t2.startdatetime and t2.enddatetime
        and t1.tripID > t2.tripID)
    select distinct tripID,
           driverid,
           vehicleid,
           startdatetime,   
           enddatetime 
    from yourTable t, cte
    where t.tripId = cte.ID1
    or t.tripId = cte.ID2