Posted by Ed Prochak on 09/21/05 23:47

pb648174 wrote:
> I think you should stick with tracking the start and end dates only but
> you will also need to track the available hours for appointments (M-F
> 9-5) so that you can do the subtraction and come up with the free time.

I think I would use a start time and duration instead of start time and
end time. Then something like

table schedule
starttime timestamp
duration number (units of minutes?)
usedflag a code type (int or char)
location
description
....

free time are those rows with usedflag code indicating it's free.
Finding the first available free slot is something like

select min(starttime)
from schedule
where usedflag='free'
and duration >= :desired_duration

or all slots on a given day

select starttime
from schedule
where usedflag='free'
and starttime between :given_day and :given_day+1day

I'm really surprised this wasn't suggested earlier.
Ed

• Next in forum: searching a list of tables, derived from another table
• Prev in forum: Re: search on concatenated fields in sql server
• Thread view: Re: Scheduling Database - Design Help!

[Reply to this message]