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

[Tab Alleman]

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)

and

"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.