Date Ranges conflict finder

[seraph_nyc]

Hi,

I am creating a calender type function. The database has a history of performance which someone is doing. The performances may be ongoing meaning stretch into next year. I want to create a way of adding a performer to a performance. But first I need to check availability and check schedule conflicts. I have each performance with a start date and end date and a calculated date range. In essence I need a way of check to see if any date ranges over lap. I am thinking of doing a script which goes thru each day of a performance and see if it falls with any other performance, but this is time consuming. I am looking into self joining with a range of dates, but it isn't quite working well. Any idea?

[Ender]

This should be possible by testing for the presence of a record over a range-to-range relationship. The relationship would look something like:

Interface <=> Performance =

Interface::gStartDate ≤ Performance::End Date

AND Interface::gEndDate ≥ Performance::Start Date

Where the dates to test are gStartDate and gEndDate.

To then test for the presence of a related record, use:

If [ isempty(Performance::RecordID) ]

#No record exists in specified range

...

End If

[seraph_nyc]

Thank you. I made a few modifications but it works. The IF(empty) record ID part did not work thou. If there is a conflict is found then all the records with a conflict is there. If there isnt one then no records are found.

[Ender]

I'm not sure what you had trouble with. The If[] step tests for the presence of related records. If there are matching records, then there is a conflict and the If[] would return 0, if there are no matching records, then there are no records with dates that overlap the specified range, and the If[] returns a 1.