I have a table with a single date column, and I'm trying to determine the range of dates for which a specific criterion is met. Here's an example of what I mean:CREATE TABLE [MyTestTable] ([MyDate] [datetime] NOT NULL,[MyCriterion] [bit] NOT NULL)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-01',1)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-02',1)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-03',1)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-04',0)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-05',1)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-06',0)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-07',1)INSERT INTO MyTestTable (MyDate,MyCriterion) VALUES ('2009-01-08',1)What I would like is a select statement that returns the start and end dates for each continuous block of dates where MyCriterion = 1 (NOTE: my table has no gaps in the dates, so from the first date to the last there are no missing dates or dates with no data). So given the above data, this select statement would return the following:StartOfBlock EndOfBlock------------ ------------'2009-01-01' '2009-01-03''2009-01-05' '2009-01-05''2009-01-07' '2009-01-08'
I'm guessing step one is simply a sub-query getting only those entries where MyCriterion = 1. Is there some way to detect which dates are missing then? I'm not sure that would help in determining the continuous blocks, though.Anyway, any suggestions on how to do this, or even where to begin would be greatly appreciated.
Continuous Block of Dates
Posted - 2009-09-02 : 03:01:09
