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

[Colin-Albert]

You need to identify the rules on how a trip relates to a shift.

If a shift 3 runs from 21:00 to 05:00, and shift 4 runs from 05:00 to 14:00,

How do you want to identify a trip that runs from  02:00 to 06:00?

     Is it associated to Shift 3 because that is when the trip starts?

     Is it associated to Shift 4 because that is when the trip ends?

     Is it associated with both shifts?

Also should you define Shift3 as running from 12:00 to 04:49:49.999 or Shift 4 as starting at 05:00:00.001 so you do not have a journey that ends at 05:00 linking to both shifts.

Until you have defined these rules, you cannot create the expressions for your charts.

[joggiek]

The trips are associated to all shifts it touches

The shift end and the next start on the same time

(Example Shift 1 - 09:00 to 21:00:00 Shift 2 21:00:00 to 05:00:00)

If a trip ends on the exact time (21:00:00) it will show as part of both shifts

[gandalfgray]

Hi Joggie

It is not clear how your shifts are defined.

(A trip at 4AM monday morning, does it belong to Shift 3 (of sunday) or to no shift?

A trip at 0:30AM monday morning does it belong to Shift 3 (of sunday) and to WeekEnd 2?)

It may be easier to find a solution if you add a column in your Shift definition file which defines which weekday(s) respective shift starts in.

/gg

[joggiek]

Hi,

The trips will belong to both shifts.

I can see the requirement change where they will ask me to split the shifts,  but that is a bridge I will cross then.

For now a single trip can belong to multiple shifts.

What I did so far was to split the trips into AM and PM sections, so the trips that spans more than 1 date are now split into a section from, trip start to 00:00:00 and the 2nd section from 00:00:00 to trip end, effectively splitting those trips into 2 separate trips.  No I just need to work out how to assign those trips to 1 or more shifts.

[jonathandienst]

Joggie

One possible way around this is to work in weeks: create an array of shift times in hours from the start of the week in a temp table, and convert all the trip start and end times to hours from the start of the week. Then do a simple interval match.

So the temp shift table for matching would be:

5, 13, WeekShift1

13, 22, WeekShift2

22, 27, WeekShift3

27, 35, WeekShift1

35, 43, WeekShift2

...

120,125, WeekendShift1

125, 132 WeekendShift2

HTH

Jonathan

[joggiek]

Hi Jonathan,

I do not understand the design you mentioned, what are the column headings in that lookup table?

Thanks

[hic]

See page 19 in IntervalMatch and Slowly Changing Dimensions

HIC

[joggiek]

I just started reading this document, will jump to page 19.  Will keep you posted

Thank you

[#10]

Hi,

PFA It will give solution for ur Problem.

Regards,

Damu