What you need is all times that are contained entirely inside an availability but do not contain any part of an appointment.

And that's actually the problem... I don't think it can be done easily without subqueries ... basically, it's a correlated query, going through each possible appointment time, and for each time, verifying that there is no intersection with one of the existing appointments, and that the time is fully contained within the employee's availability.

This is one of those times when it's nice to have shelled out all of the extra cash for Oracle. Some databases might be able to handle a single sub query, other might handle more than one, others might be able to cheat using views, etc. If you're not using Oracle, you might have to pull everything back to Perl ... well, not everything...all you really need is a single 'yes/no' response, which I'd probably just use a count of the number of appropriate intersections/containments.

To help out, here's the little cheat sheet that I keep buried in one of my programs to refer back to when I'm dealing with range matching:

=pod

=head2 Interval Matching

 .........A============B......... Search Range
 ...x--y..|............|......... (never match)
 .........|............|...x--y.. (never match)
 ......x--|--y.........|......... (1)
 ......x--|------------|---y..... (2)
 .........|...x-----y..|......... (3)
 .........|..........x-|----y.... (4)

=over 4

=item Intersection

A <lt>= y && B <gt>= x

matches examples 1,2,3,4

=item (item fully contained by search range)

A <lt>= x && B <gt>= y

matches example 3 only.

=item (item fully includes search range)

A <gt>= x && B <lt>= y

matches example 2 only.

=cut
[download]

Update: I stand corrected on the need to do it in mutliple selects, as davidrw has shown