Re: SQL Query for Scheduling

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]

- Darren

Comment on Re: SQL Query for Scheduling Download Code

Replies are listed 'Best First'.
Re^2: SQL Query for Scheduling by TedPride (Priest) on Aug 16, 2005 at 08:56 UTC
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.	[reply]
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]