algorithm for finding overlapping times 1

[teering]

i am needing an algorithm to find overlapping times on data entry, for example, services are provided from 8 a.m. to 9 a.m. by one person and another person enters they provided services from 8:30 to 9:30 (and various other overlaps of time) finding duplicates is no problem, but i haven't been able to find an efficient routine for overlapping times
i am using vfp 7
also the times are entered in military time such as 13:00, 15:00 etc.
thanks in advance




Teresa Pickering
Professional Computer Services

 

[TamarGranor]

I'd think you'd need to do this with a self-join of some sort. This is untested but should give you the idea:

SELECT <relevant fields> ;
  FROM YourTable YT1 ;
    JOIN YourTable YT2 ;
      ON <whatever conditions indicate the same provider> AND;
         (YT2.tStart < YT1.tEnd OR ;
          YT1.tStart < YT2.tEnd) ;
  INTO CURSOR Overlaps

IOW, having matched the other conditions, look for combinations where the start of one comes before the end of the other.

Tamar

 

[Olaf Doschke]

not quite, Tamar,

yt1.start 10:00
yt1.end 11:00

yt2.start 12:00
yt2.end 13:00

yt1.start<yt2.end is given, still those time intervals don't overlap.

the condition is:

(yt1.start between yt2.start and yt2.end)
OR
(yt1.end between yt2.start and yt2.end)
OR
(yt2.start between yt1.start and yt1.end)
OR
(yt2.end between yt1.start and yt1.end)

This way you will always have two records in the result for each interval overlap, if you want each overlap only once, also check yt1.id<yt2.id.

in each interval pair, the min and max times define the overal iterval, the second and third time in chronological order are the overlap. You might not want results, where the intervals just touch. Then you could use two comparisons with < instead of each between (which is like comparisons with <=) or check between start+epsilon and end-epsilon with epsilon being a small time amount.

Bye, Olaf.

 

[Mike Lewis]

Also, this assumes that the test is sensitive to the date.

That is, Tamar's algorithm will say that 8.30 to 9.30 overlaps 8.00 to 9.00 on the same day, but not if they are on different days. I expect that this is what Teresa wants, but it's worth making sure.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[alvechurchdata]

i haven't been able to find an efficient routine for overlapping times

I may be reading too much into this statement but are you saying that you already have an inefficient routine? Can you show us how you're solving the problem now.

Do you have to cater for time periods across midnight? Don't forget that twice a year this involves the switchover from standard to daylight saving and back again.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Don't forget that twice a year this involves the switchover from standard to daylight saving and back again.

In which case, the code would also have to be sensitive to the region or country in which the times apply.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

Thanks, Olaf, good catch. I knew it didn't feel right, but didn't stop to think about why.

Tamar

 

[NasibKalsi]

Just to check the duplicates for a day (24:00)
The following logic may be helpful

If date is stored in the table, then yopu can also use date and time combination to take care of any odd situation by setting the date and time to be starting point and go up to 24 Hours there onwards or any number of hours there after. Change 24 to any other number of hours to track.


dimension a_OverLap[24*60]

a_OverLap = 0 && Initilize all to 0, use for loop


do while .not. eof() && Or use scan
mmBegin = Time2Minutes(STARTTIME)
mmEnd = Time2Minutes(ENDTIME)
* Offset it By 1 because we can not address location[0]
* Make sure you take care of this in other places
*
for ii = 1+mmBegin to 1+mmEnd
a_OverLap[ii] = a_OverLap[ii] + 1
endfor
skip
enddo

* Now examine your Array
* 0 : No body was there at that time
* 1 : Only One person was present
* 2 : Two Peoples were there
* and so on
*
* Print OverLaps
for ii = 1 to (60*24)
if a_OverLAp[ii] > 1
*? "OverLapping",Minutes2Time(ii),a_OverLap[ii]
? "OverLapping",ii,a_OverLap[ii]
endif
endfor


function Time2Minutes
lparameters p_Time
* do error checking if necessary
return val(p_Time) * 60 + val(right(p_Time,2))
endfunc


try it

* Not tested

 

[NasibKalsi]

Teresa:

I reread your post, i am a bit confused ...

1. Do you want to correct the data as it is being entered, i.e., issue an error message "Time over Lap" etc.

2. Or testing after the data is entered ?

 

[teering]

sorry, i don't have a routine at all for the overlap times

i do have a procdure for duplicate times that is run as an edit after the services have been entered then a separate report prints them out

i am looking at a 24 hour period (which i did not take into
account midnight, 24:00

the scenario is for several different workers to enter times
of service provided for someone during the 24 hour day
some services are entered directly to the main server and
some on laptops that are imported the next day

i would need two routines one to check data as it is entered (on the server) with an error message and then a procedure to check the data after it is entered and i can create a report to print out the overlaps (like i do the dups)

i have created a method with the code provided by olaf - and in testing does seem to catch overlaps - thanks olaf -

but i will need to consider midnight and the time changes during the year also

thanks in advance



Teresa Pickering
Professional Computer Services

 

[Olaf Doschke]

DaylightSaving Times is a problem, if you only store date and a military time string like "02:05". You can't decide then, if it's the first "02:05" or the second one of the same date. Several intervals like 2:30-2:50 and 2:50-2:30 would indicate, that the first one would be before time shifts back.

At the days, where time shifts forward the problem is easier, you just need to subtract an hour from the intervals starting before and ending after the time shift.

the midnight problem is solvable by combining date and military time to a datetime value.

Bye, Olaf.