Complex Trainer availabilty logic

[jlisburn]

Part of my solution needs to advise users which are 'good' days to book particular trainers, and which are 'bad'. (Good or bad depends on distance from trainers home and whether the trainer can train that product.)

(I've built a matrix of post codes -> Regions and have a join table vs Trainer (i.e. Trainer A likes region A, Trainer A doesn't like Region B, Trainer hates Region C etc)

(I also have a 'suitability' join table of trainers to products)

My issue then becomes relating which days these trainers are not otherwise occupied or on holiday/sick/PD etc)

I can't see past having to create an 'availability' table with every day, every trainer, AM/PM combo possible, and deleting them as they are booked (or book holidays, PD) but this seems inefficient....

.... but then (if I have a sick days table, vacation table etc) I can't wrap my head around what kind of query/calculation/script I'd use to generate my answer (I'm guessing I need something SQL-like).

Q: Given this region and this product which days are trainers ideally available?

A: ideally would be a list of 'good' days, for which appropriate trainers are available - delivered as a Text Tool seen when hovering over a repeating field of 'suggested' dates the user is then gonna send to a client

Thanks for any help

[comment]

1. IIUC, region and product have nothing to do with dates. These are three separate criteria by which to select the available trainers, aren't they?

2. Any day that isn't bad is good. This means you only have to store the dates on which a trainer is unavailable. If, however, you need a list of good days, you will have to generate it on-the-fly. I didn't understand from your description how exactly this should work - surely there must be a "from - to" limit?

[jlisburn]

Hi, thanks,

1. Yep, they tell us which trainers can be used, then we need to find the availability (dates) of those trainers.

2. 'From - to' limit - kind of, there is a default period in which we'd be looking (next 7-30 days or so) I was thinking to break things up by Business Week?

Unless I have an availability table, I'll have to search multiple tables in order to build up a picture of when the trainer is unavailable; and then from that extrapolate, the other days are when he is available (Show Omitted records kinda-thing)

So I'd be looking at a virtual list? Jumping from table to table to collect 'bad' dates and then parsing through them to report the good dates?

Thanks again