Checking if event occurs between another list of start and end datetimes.

[mgreen76]

I have two tables, one containing a schedule of events.  For this example it would consist of :-

EventID, Event_Start, Event_End

701,  11/09/2019 10:00:00, 11/09/2019 13:00:00

702,  12/09/2019 13:00:00, 12/09/2019 17:00:00

703,  12/09/2019 19:00:00, 12/09/2019 21:00:00

My other table is a list, lets say incidents which may occur during any time of the day.  This table would look like

Incident_ID,  Incident_Start, Incident_End

9001, 11/09/2019 11:20:00, 11/09/2019 11:45:00

9002, 11/09/2019 08:50:00, 11/09/2019 10:30:00

9003, 12/09/2019 14:00:00, 11/09/2019 18:15:00

9004, 12/09/2019 11:20:00, 11/09/2019 11:45:00

9005, 11/09/2019 11:50:00, 12/09/2019 20:30:00

What I need to determine is whether any of my incidents have occured at any point during each event.  This should indicate the following.

Incident 9001 - Occurred during event period 701

Incident 9002 - Occurred during event period 701

Incident 9003 - Occurred during event period 702

Incident 9004 - Did not occur during any event period

Incident 9005 - Occurred during event periods 701,702,703

What I'd like to be able to do is calculate the during of incident time against each of the events.  I had been looking around the intervalmatch function but don't think this would work.

[lironbaram · Accepted solution]

hi 

interval match is absolutely your solution 

what i did is to convert the incidents list to a table with incident id and running date with 5 minutes interval 

and than used interval match 

vents:
Load EventID,
Timestamp(Timestamp#(Event_Start,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Event_Start,
Timestamp(Timestamp#(Event_End,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Event_End;
load * Inline [
EventID, Event_Start, Event_End
701, 11/09/2019 10:00:00, 11/09/2019 13:00:00
702, 12/09/2019 13:00:00, 12/09/2019 17:00:00
703, 12/09/2019 19:00:00, 12/09/2019 21:00:00
];

incidentsTemp:
load Incident_ID,
Timestamp(Timestamp#(Incident_Start,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Incident_Start,
Timestamp(Timestamp#(Incident_End,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Incident_End;
load * inline [
Incident_ID, Incident_Start, Incident_End
9001, 11/09/2019 11:20:00, 11/09/2019 11:45:00
9002, 11/09/2019 08:50:00, 11/09/2019 10:30:00
9003, 11/09/2019 18:15:00, 12/09/2019 14:00:00
9004, 11/09/2019 11:45:00, 12/09/2019 11:20:00
9005, 11/09/2019 11:50:00, 12/09/2019 20:30:00
];

incidents:
load Incident_ID,
Timestamp(Incident_Start +(iterno()-1)*5/1440,'DD/MM/YYYY hh:mm:ss') as Incident_timeStamp
Resident incidentsTemp
While Incident_Start +(iterno()-1)*5/1440<=Incident_End;

IntervalMatch(Incident_timeStamp) load Event_Start,Event_End Resident events;

[mgreen76]

Thanks, I'll work around this.