There is an assumption that the number of intervals in an hour is constant, at least for the run of that day. But if different hours have different increments then the whole thing will fall apart because you can't determine how many records you should expect for the one hour time period.


>>Just to add a further note, you can calculate the number of intervals by replacing the cnt = 4 with a query such as this:
>>
>>

>>select (MAX(cnt)) from
>> (select cnt = count(*)
>>  from onehour
>>   group by resgroup, respk, Substring(time, 1, 2)) as a
>>

>>
>>The basic logic I tried to use was
>>
>>For each record in the table, how many records with that time or greater (within the one hour time frame) have the resused as false?
>>If that count is equal to the number of intervals in an hour then we have a starting time for which we have one hour of available time. Of course, the very next interval may also provide a one hour window as well.
>>
>>
>>>>> Do you always have the fifteen minute intervals?
>>>
>>>No. The records are built during a "midnight run" process and the time interval is based off how the resource is defined. It is in at least 5 minute intervals. The min time a resource is allowed to be scheduled is 5 minutes and the interval is incremented in 5 minute increments.
>>>
>>>Thanks for your thoughts
>>>
>>>Kirk
>
>If there is a record for every 5 minute interval, your solution will probably run faster than mine, though I don't know. If there are gaps, I don't think it works.
>
>Interesting variation.