I am making a scheduling app to help book singers on holiday caroling jobs. I have two tables (how they are set up can be seen here: http://www.wrightengineers.com/mytables.jpg
). The Jobs table has start and end times for each job. The SingerSchedule table has start and end times of periods of time that singers are NOT available, in other words, where they have previous commitments. I am having trouble writing code to tell me which singers are available to book which jobs.
So, if I have a singer who is NOT available 9a-5p, I want to be able to find out if they can work a job that is 6p-10p. Or, if the singer is completely open all day long, there won't be an entry in the singerschedule table since they've only been entering conflicts & previous commitments. To further complicate things, each job gets a quartet: soprano, tenor, alto, bass. Only one of each type (S,A,T,B) can be assigned to one job, for a total of four singers.