[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