Den 2011-09-22 04:48 skrev red_october2009 såhär:

> I'm building a little scheduler, with FB 2.5 as my DB. For reasons which
> I will not get into, the design I went with is to create a table that
> contains nothing more than a list of minutes in a given 24 hour day and
> an "Available" flag. The table DDL is as follows:
>
> CREATE TABLE MINUTE_LIST
> (
> DT DATE, <- Contains the date
> TM TIME, <- Contains the time (to the minute)
> TS TIMESTAMP, <- (Same Date/Time value as in DT and TM just here it's
> together in a TimeStamp)
> AV CHAR(1) DEFAULT 'N' <- Flag showing if this minute is available
> )
>
> Sample data:
>
> 2011-09-21 13:24 N
> 2011-09-21 13:25 N
> 2011-09-21 13:26 Y
> 2011-09-21 13:27 Y
> 2011-09-21 13:28 Y
> 2011-09-21 13:29 N
> 2011-09-21 13:30 N
> 2011-09-21 13:31 N
> 2011-09-21 13:32 Y
> 2011-09-21 13:33 Y
> 2011-09-21 13:34 N
> 2011-09-21 13:35 N
> 2011-09-21 13:36 N
> 2011-09-21 13:37 Y
> 2011-09-21 13:38 Y
> 2011-09-21 13:39 Y
> 2011-09-21 13:40 Y
> 2011-09-21 13:41 N
>
> I have a task that is 3 minutes long that I need to fit into this list
> some where, in the spots that are "Y" (Available) and are at least 3
> minutes in a group. (By observation, I can see there are only 2 places
> that can hold a 3 minute task, 13:26 (Exactly 3 minutes) and 13:37 (4
> minutes), but definitely NOT 13:32, because it is only 2 minutes)
>
> What nifty SQL can I use to extract a list of "start" minutes that are
> followed by at least 3 available minutes (including the start minute).

select ts
from minute_list m
where (select count(*)
from minute_list s
where s.ts >= m.ts
and s.ts <= dateadd(:3 - 1 minute to m.ts)
and s.av = 'Y'
) = 3

Replace 3 in two places with whatever interval length you need to schedule.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64