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

In reply to Re^3: SQL Query for Scheduling by jhourcle
in thread SQL Query for Scheduling by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.