Scheduling dilemma with multiple start and stop dates
I am building a database for a company that has screens in restaurants that run multiple video ads. Nine ads that run simultaneously on each screen. The position of the ads on the screen does not matter. The advertisers contract for a block of months to be displayed on the screen, and can start at either the first or middle of the month and can run for any number of months. The sales reps are going to use this in the field to schedule the ads. They need to know if there is an open slot for the length of time that the advertiser requests. The Sales Reps also book screens in the future, they may book for several months down the road. If the screen fills up for a particular range of dates, I need to be able to notify the users that the requested start and stop dates are not available, and ultimately, return the open dates that are open.
My issue is how to write a routine for checking multiple start and stop dates and compare it to the requested dates. For example, let's say there are only three ads running at a time, Advertiser A wants Jan. - April, Advertiser B wants May - July, Advertiser C wants Jan - December. If Advertiser D wants Jan - December, it's open (Jan.- April from Advertiser's B and May - December from Advertiser's A) but guys, I am stumped on how to write an algorithm for combining open dates and adding them together to see if a requested start and stop date is indeed open.
My contract record holds the start and stop dates, and a related "Screen" table holds the advertiser's names, start and stop dates. I currently only have nine separate fields in the Screens record, but I assume I will build another related table to hold more than 9 advertisers, as the Sales Reps can book a screen for future dates.
It goes on, there are multiple screens in one restaurant (these are in the bathrooms and bar) and an advertiser can advertise on any screen, and so forth, but I've got that written.
I've been using FileMaker for well over 15 years, but this one has popped my cork, and any assistance would be tremendously beneficial.