Detect and calculate the overlap of the Stops with the Batches.

[stj1120]

Hi all,

I have a data set where it has Batch Numbers, Batch Start Time(ActualStart) and Batch End Time(ActualStop).

Also it has the Stops which means that there will be an off in while the production is running due to some cause.  

Therefore, to know when the Stops start and end we have StopStartDateTime and StopEndDateTime columns.

There are Stops in this data set with the stop cause during the batch.

For example:  One stop that begins 2 minutes before the batch starts but lasts until almost 23 minutes after batch start. And another that starts 2 minutes before the batch ends. Together, they have around 25 minutes of overlap with the batch. 

So, from this data set I'd need to find the following: . 

1. Detect all stops that overlap with each batch, also if they begin before the batch or end after
2. Calculate how much of each stop is actually inside the batch (if it only overlaps partly) 
3. Note that a stop may overlap with more than one batch. 

Could you help me how to find these things with the workflow? I tried many ways but didn't get any proper solution. 

Thanks in advance.

Best Regards,

Teja

[BrandonB · Accepted solution]

I think that this workflow should solve it for you. Picture of workflow is below and workflow is attached. The formula that I used for the batch overlap calculations is (as well as a description after each block of the conditional explaining what that part does):

IF [StopStartDateTime]>=[ActualStart] AND [StopEndDateTime]<=[ActualStop]
THEN DateTimeDiff([StopEndDateTime],[StopStartDateTime],"minutes")
/*
Stop starts after actual start and stop ends before actual end
*/
ELSEIF [StopStartDateTime]<=[ActualStart] AND [StopEndDateTime]<=[ActualStop]
THEN DateTimeDiff([StopEndDateTime],[ActualStart],"minutes")
/*
Stop starts before actual start and stop ends before actual end
*/
ELSEIF [StopStartDateTime]<=[ActualStart] AND [StopEndDateTime]>=[ActualStop]
THEN DateTimeDiff([ActualStop],[ActualStart],"minutes")
/*
Stop starts before actual start and stop ends after actual end
*/
ELSEIF [StopStartDateTime]>=[ActualStart] AND [StopEndDateTime]>=[ActualStop]
THEN DateTimeDiff([ActualStop],[StopStartDateTime],"minutes")
/*
Stop starts after actual start and stop ends after actual end
*/
ELSE Null()
ENDIF