[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.
Non-technical:
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:
________SCHEDULERANGE__________
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
http://mattwarden.com
This email proudly and graciously contributes to entropy.
More information about the thelist
mailing list