[thelist] tough: sql solution for finding available days for anappointment

Tab Alleman talleman at Lumpsum.com
Tue Apr 11 14:39:35 CDT 2006

Seems like you're saying two different things about your objective:

"I am determining whether an appt is available on this day..." (return true or false)


"select dates if it doesn't exist in the set of dates in the next two months..." (return a date range).

The first should be relatively easy.  As for the second, I agree with the other poster that the most elegant way to do this is with a utility table of dates.  Rather than maintaining one indefinitely, you could have your stored procedure create/destroy a temporary table for the next two months worth of dates each time it is called.

thelist-bounces at lists.evolt.org wrote:
> 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