Hi Kirk,

The reason is that you're doing a one to many relationship. The first record in the TC table will pick up all records except 07:00, 07:15, 07:30 & 07:45 which is correct and the second TC record will pick up all records except 08:00, 08:15 & 08:30, but the second TC record will also pick up 07:00, 07:15, 07:30 & 07:45. This is better illustrated if you run the following code:

select tc.scheduledstart, tc.scheduledend, ttoc(ts.timeslotdesc,2) ;
from ts,tc ;
where !between(ttoc(timeslotdesc,2),ttoc(scheduledstart,2),ttoc(scheduledend,2));
into cursor ts1
brow norm


I think you'd be better off creating a cursor or temporary table with the same structure and data as TS and an extra field called TIMEEXISTS which is a logical, then scan through the TC table and mark TIMEEXISTS with .T. in the temporary table if the time is within the range of the TC table. After the scan you'll be able to pull out all the records from the cursor which have TIMEXISTS = .F. and that should hopefully give you the slots that are free.

Hope this helps,
Gavin...

>I have two tables. The first table TS (TimeSlots) has the column TimeSlotDesc defined as a datetime field. Right now there are 55 records that have valid time slots:
>
>03/15/2000 00:15:00
>03/15/2000 00:30:00
>03/15/2000 00:45:00
>...through
>03/15/2000 13:15:00
>
>These records reflects time slots that the user can choose, unless that slot is already reserved. The second table is SC (SlotsScheduled), and it contains a ScheduledStart (datetime) and ScheduledEnd (datetime). They are not linked. There are two records (early testing) that have the following scheduledstart and scheduledend:
>
>ScheduledStart ScheduledEnd
>03/15/2000 07:00:00 03/15/2000 07:45:00
>03/15/2000 08:00:00 03/15/2000 08:30:00
>
>What I'd like to return is a cursor that displays time slots
>that are not occupied by the SlotsTaken, i.e. not display the slots
>between 07:00-07:45 and 08:00-08:30
>
>Here is the SQL-Select i've tried, limiting the results to slots
>greater than 05:00
>
>select ttoc(ts.timeslotdesc,2) ;
> from ts,sc ;
> where !between(ttoc(timeslotdesc,2),ttoc(scheduledstart,2),ttoc(scheduledend,2));
> and ttoc(ts.timeslotdesc,2)> '05:00:00' ;
> order by timeslotdesc into cursor ts1
>
>Results: All records returned
>05:15-13:15
>
>If I delete the second record, I get what I want:
>05:15:00
>05:30:00
>05:45:00
>06:00:00
>06:15:00
>06:30:00
>06:45:00 && Leave the 07:00-07:45 Out
>08:00:00
>08:15:00
>08:30:00
>08:45:00
>
>Any help greatly appreciated.
>
>Kirk