I have over 5000 time entries and I'm trying to check for overlaps on the same dates. I've tried using a date overlap script that I saw on here, but I cannot get it to work for the time.
That data looks like this:
DATE
FIRST TIME IN
FIRST TIME OUT
2015/12/07
16:15:00
18:15:00
2015/12/07
19:00:00
21:00:00
2015/12/10
07:30:00
09:30:00
2015/12/10
12:30:00
14:30:00
2015/12/10
14:45:00
16:45:00
This is the script that I tried:
@IF( DATE =@GetNextValue("DATE"), @AGE(@GetNextValue("FIRST_TIME_IN"), FIRST_TIME_OUT), 0)
Hi CFE4130,
I used your data to create this database, I made a change on the last row so that it would overlap with the previous record.
I created two fields, OVERLAP_NEXT to indicate if the transaction overlaps with the next items and OVERLAP_PREVIOUS to indicate if it overlaps with the previous. You can then do an extract on these fields to have the items that overlap.
Here are the two equations:
@if(date = @GetNextValue("DATE") .AND. FIRST_TIME_OUT > @GetNextValue("FIRST_TIME_IN"), "Yes", "No")
@if(date = @GetPreviousValue("Date") .AND. FIRST_TIME_IN < @GetPreviousValue("FIRST_TIME_OUT"), "Yes", "No")
You can see they are pretty much the same, one uses the @GetNext and the other uses the @GetPrevious. It just checks to see if the time in is before the time out or vice versa depending on the functions. Now for this to work the files must be in the proper order so you would want it indexed by Date and FIRST_TIME_IN for this to work properly.
Let me know if this is what you are looking for.
Brian