[thelist] tough: sql solution for finding available days for an appointment

Matt Warden mwarden at gmail.com
Tue Apr 11 13:26:49 CDT 2006

Hi list,

I am working on a scheduler, and the part I'm working on now deals
with finding days available for an appointment, matching preferences
requested by the user. The db is MySQL 5.

A user specifies the services needed (e.g. for a salon, haircut and
color). Then the user specifies provider preferences for each service
(e.g., Pam or Sue for haircut, Chuck for color). Then the user
specifies time of day preference(s) (e.g., morning or late evening).

Here's what I'm doing now.
I'm getting data from the database that gives me a picture like this
for each day:

Pam  [                  [|||||||appt||||||||]       
[||||||appt|||||][||||||appt||||||||]              ]
                                                     [midday range]

Now the user has specified mid-day (range above). I am determining
whether an appt is available on this day by summing the time required
for each service requested and seeing whether the difference between
the time of day preferences and any unavailable time range is greater
than or equal to this sum. I need only binary knowledge: either there
is an availability on this day or there is not.

I have this working (mostly). But I can't help but think there is a
much more elegant solution in SQL that would make adding additional
features 100000x easier. The problem is that the original set (set of
all days in the next x months) doesn't exist in the database, only the
set of unavailable times (appointments or vacations, etc.) in the next
x months.

Here's the simplified table structure:

SCHEDULERANGEID	int(10) unsigned
OFFICEID	int(10) unsigned
STATUSID	int(10) unsigned
PATIENTID	int(10) unsigned
PROVIDERID	int(10) unsigned
BEGINDATE	datetime
ENDDATE	datetime

Something like this is what I'm going for:

select date
from ???
where date not in (
    select date(enddate)
    from schedulerange
    where enddate between now() and date_add(now(), interval 2 month)
      and (subtime(time(begindate), $timerangestart) >= $timeneeded
                  || subtime($timerangeend, time(enddate)) >= $timeneeded)

(in english: select dates if it doesn't exist in the set of dates in
the next two months where the difference between the begin time of the
appointment and the begin time of the requested time range (e.g.,
mid-day) or the difference between the end time of the appointment and
the end time of the requested time range is larger than the time
needed for the requested services.)

Any thoughts? Even if you don't have a full solution, ideas concerning
the direction I should go would really help out.

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list