in reply to Re: SQL Query for Scheduling
in thread SQL Query for Scheduling

Hmm, but what about availability? You left that out entirely in the query. Also, you seem to be assuming that all appointments will start and end at exactly the same times each hour and that appointments will all be the same length. Since time ranges are being used, this is obviously not the case, and things have to be more linear.

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

Replies are listed 'Best First'.
Re^3: SQL Query for Scheduling
by jhourcle (Prior) on Aug 16, 2005 at 12:02 UTC
    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:

    Read more... (860 Bytes)

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

[reply]
[d/l]

In Section Seekers of Perl Wisdom