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