SELECT
  t.start,
  t.end
FROM appointment_times t
  JOIN scheduled_availability s ON
    AND t.start >= s.start
    AND t.end <= s.end
  LEFT JOIN appointments a ON
    a.EmployeeID = s.EmployeeID
    AND t.start <= a.end
    AND t.end >= a.start
WHERE a.EmployeeID IS NULL
;
[download]

Thanks! The real life query is a little bit more complicated than what I described in my question, but I think this will work perfectly.

Seems to me that this is an SQL question rather than a perl one, but anyway...

Here is one way to do it using mySQL (if I've understood the question correctly)

Your question was: "I want to find which appointment times are still available"

Therefore, the scheduled_availability table isn't required.. ie. you only need to do a select from two tables

CREATE TABLE scheduled_availability (
  employeeID int(11) NOT NULL default '0',
  start datetime default NULL,
  end datetime default NULL
) TYPE=MyISAM;

CREATE TABLE appointments (
  employeeeID int(11) NOT NULL default '0',
  start datetime default NULL,
  end datetime default NULL
) TYPE=MyISAM;

CREATE TABLE times (
  start datetime default NULL,
  end datetime default NULL
) TYPE=MyISAM;


INSERT INTO times VALUES ('2005-08-16 16:35:40','2005-08-16 17:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 17:35:40','2005-08-16 18:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 18:35:40','2005-08-16 19:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 19:35:40','2005-08-16 20:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 20:35:40','2005-08-16 21:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 21:35:40','2005-08-16 22:35:40')
+;
INSERT INTO times VALUES ('2005-08-16 22:35:40','2005-08-16 23:35:40')
+;

INSERT INTO appointments VALUES (1,'2005-08-16 16:35:40','2005-08-16 1
+7:35:40');

mysql> select times.start, times.end from times, appointments where ap
+pointments.start != times.start and appointments.end != times.end;
+---------------------+---------------------+
| start               | end                 |
+---------------------+---------------------+
| 2005-08-16 17:35:40 | 2005-08-16 18:35:40 |
| 2005-08-16 18:35:40 | 2005-08-16 19:35:40 |
| 2005-08-16 19:35:40 | 2005-08-16 20:35:40 |
| 2005-08-16 20:35:40 | 2005-08-16 21:35:40 |
| 2005-08-16 21:35:40 | 2005-08-16 22:35:40 |
| 2005-08-16 22:35:40 | 2005-08-16 23:35:40 |
+---------------------+---------------------+
6 rows in set (0.00 sec)
[download]

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.

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

CREATE TABLE employee (    -- Lists availability times
    ID       NUMERIC,
    start    DATETIME,
    end      DATETIME
);
CREATE TABLE scedule (     -- Lists scedules appointments
    ID       NUMERIC,      -- Foreign key into employee
    start    DATETIME,
    end      DATETIME
);
CREATE TABLE appointment ( -- Unsceduled appointments
    ID       NUMERIC
    start    DATETIME,
    end      DATETIME
);

SELECT employee.ID, appointment.ID FROM employee, appointment
WHERE  employee.start <= appointment.start AND  -- Check availability
       employee.end   >= appointment.end   AND
       employee.ID NOT IN (  -- Exclude employees who 
                             -- already have an appointment
             SELECT scedule.ID FROM scedule, appointment
             WHERE  scedule.start <= appointment.end AND
                    scedule.end   >= appointment.start
       )
[download]