Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Thanks, I'll work around this.