checking times with in a range of different time periods - need help plz..

[lavanya14]

Hi All,

I would like to know if there is any way to check if the two different times fall with in a range of time period. Let me explain you with an example..

Lets say i have different slots 

Name          working - starttime                       working-  endtime

ABC          30/10/2014 06:00:00                    30/10/2014 08:00:00

ABC          30/10/2014 10:00:00                    30/10/2014 12:00:00

XYZ           30/10/2014 08:00:00                    30/10/2014 10:00:00

Lets say a delay has happened for 15mins and the start time of delay is 30/10/2014 07:50:00 and the end time would be

30/10/2014 08:05:00. this delay falls in the range of both ABC and XYZ working time slots.

I wanted to know if there is anyway i can pass the start time and endtime of delays to the working-starttime and working - endtime at the same time so that i can find if the delay is falling in two different persons working times or not.

Any suggestions are appreciated..

Thanks in advance.

Using interval match i cna find out if  

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[lavanya14]

Thanks alot  Gysbert !!

[lavanya14]

Hi Gysbert,

Just a small clarification.. This solution works good when i have less no of data but my data is huge and the application is hanging.. any suggestions plz?

[Gysbert_Wassenaar]

Yes, I kind of expected that. The solution creates a cartesian product first and then removes most records again by comparing the date fields and keeping only those where the intervals overlap. Perhaps you can process your source data in batches, for example per month or per day.

Another approach would involve using the intervalmatch function. See attached qvw. But the intervalmatch function and the joins may also have performance problems.

[lavanya14]

Thank you !! I have a questions lets say if there is a work done at 30/10/2014 12:00:00.

Name               shift start                              shift end

abc               30/10/2014 10:00:00              30/10/2014 12:00:00

xyz               30/10/2014 12:00:00              30/10/2014 14:00:00

the move falls under both the drivers.. I think the interval match checks >= shift start and <= shift end.. Plz correct me if i am wrong.. i have handled this by making the shift end as 30/10/2014 11:59:59 ... any more suggestions?

thanks