Subject | A fun little "Time" puzzle |
---|---|
Author | red_october2009 |
Post date | 2011-09-22T02:48:22Z |
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.
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.