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

Matt Warden mwarden at gmail.com
Tue Apr 11 14:48:09 CDT 2006


On 4/11/06, Matt Warden <mwarden at gmail.com> wrote:
> 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)
> );

This isn't quite right (pretty bad when one gets the pseudocode wrong).

The subquery is negated, so I want days where there is no possibility
for an appointment. Thus, this should be the subquery pseudocode:

select date(enddate)
from schedulerange
where subtime(time(begindate), $timerangestart) < $timeneeded
              || subtime($timerangeend, time(enddate)) < $timeneeded


full query pseudocode:

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

Mattias, thanks for your suggestion about the utility table. I think I
am going to have to end up doing that, if I do come up with a SQL
solution.

Any other ideas?

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