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). Thanks in advance for any help you can provide.
