Working with overlapping date-ranges

[joggiek]

Hi All,

My company tracks its vehicles with GPS devices that store all kinds of info, but for this query I’m only interested in the trip times.

I have a table that stores the start and end times of all the trips.

With another table that stores the shifts.  It only shows the shifts and the start and end times. (No dates)

Weekend-shift 1 starts on Friday 1am to Saturday 1pm

Weekend-shift 2 start on Saturday 1pm to Sunday 1am

I need to be able to show the trips done and also to which Shifts the trips belong.  I used IntervalMatch to make this work before, but since then weekend-shifts were added that overlaps with the times of the week-shifts.

I’m stuck and hope somebody can help

The problems I have are:

The trips that run in shifts that span more than 1 day (Shift 3 that runs from 21:00 to 05:00 the next day)

Determining if the trip dates are in the week or weekend

The trips that fall in a weekend-shift where the times overlap with the week-shifts

The trips that fall into more than 1 shift, I should be able to show all the shifts the trip belongs to

I must also be able to select a shift and see the trips that were done in the selected shift(including partial trips)

I also foresee trouble with trips that start in a week-shift and end in a weekend-shift

I must also be able to select a shift and see the trips that happened in that shift(Including partial trips)

See the attachments for data examples

[hic · Accepted solution]

Try the attached script.

HIC

[joggiek]

Thank you HIC,

I tried the script, but it still seems that the trips that are split over 2 days are not working correctly.

If I select TripId 0A0BD22B-63C0-11E3-8FC3-005056830020 that:

Starts 2014-02-07 20:14:57

Ends 2014-02-08 02:34:57

The results shows that the trip was not part of any shifts, but it should be part of

Shift 2 - 20:14:57 to 20:59:59

Shift 3 - 21:00:00 to 11:59:59

Shift 3 - 12:00:00 to 02:34:57

WeekEnd 2 - 20:14:57 to 11:59:59

WeekEnd 2 - 12:00:00 to 00:59:59

WeekEnd 1 - 01:00:00 to 02:34:57

The same with 0A0A5BBC-5729-11E3-8FC3-005056830020 - 2014-02-05 22:32:01 to 2014-02-06 03:48:01 and a few more?

It looks like it is all the trips that start after 11:59:59 AM?

Maybe I'm still missing something

Thanks for the help

[joggiek]

Thank you so much for the help.  I got the shifts to work as you explained it HIC.

[joggiek]

Hi HIC,

Just a follow-up question.

I have 5 Shifts and 340 000 plus trips, the code is creating an "bridge" for every second from the first to last date.

Is there a way of loosing or trimming the seconds, and only use 'hh:mm'

I tried time(mytime,'hh:mm') , but it does not seem to work

[hic]

The Time() function will just format the time, e.g. not show the seconds, but it will not change the underlying value. If you want to truncate every time stamp to nearest minute, you should do

     Time(Floor(mytime,1/24/60), 'hh:mm')

However, doing this may cause some rounding problems: The start and end times that contain seconds will lose this information and a trip may then be attributed a shift that ended seconds before the trip started. But if you are OK with this, you should use the above expression.

HIC

[joggiek]

Thank you

I have to do this, if I don't the bridgetable builds 340 000 000 rows and is very slow.

I hope that this will create a lot less records.

[arasaraja_cts]

For the benefit of all, can you please share what HIC explained to you ?

Because I have the same problem which you had after following the script. Trips that are split over 2 days are not getting captured.