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

[Mattias Thorslund]

One thought: a utility table for the dates.

If you need a list of dates, you could make a utility table that
contains those dates. "Future dates" is a pretty a large set, but you
can make your code (think stored procedures) extend the data in the
utility table as needed.

Utility tables aren't "elegant" maybe, but if it helps in turning a
complicated query into a simple one, they can often improve performance
significantly.

A step in the right direction?

Mattias


Matt Warden 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.
>