Find continuous periods with from and to dates 1

[SimonSellick]

Hi,

I've been worrying at this one for a while without result. The person responsible for making it work resorted to a cursor but I feel that it should be possible to develop a set-based query.

In essence: there are records recording periods worked, each with a start and end date. There may be overlaps and gaps. I need a list of continuous periods defined as the earliest start date to the latest end date where no gap of 1 or more days exists. Note that a pair of records such that the start date of one immediately follows the end date of the other counts as continuous.

Here is some sample data:

id   scenario  from_date   to_date
1    S01       2001-01-01  2001-01-31
2    S01       2001-02-01  2001-02-28
3    S01       2001-01-10  2001-02-09
4    S01       2001-01-20  2001-01-31
5    S01       2001-02-10  2001-02-19
6    S02       2001-04-01  2001-04-30
7    S02       2001-05-01  2001-05-31
8    S03       2001-07-01  2001-07-31
9    S04       2001-09-01  2001-09-19
10   S04       2001-09-10  2001-09-30

And this is what I would expect as the result:

period start_date   end_date
S01    01-Jan-2001  28-Feb-2001
S02    01-Apr-2001  31-May-2001
S03    01-Jul-2001  31-Jul-2001
S04    01-Sep-2001  30-Sep-2001

I have added id, scenario and period to show where the data came from; they aren't necessary in either input or output.

I have tried the things that seemed obvious, but unsuccessfully. Any suggestions appreciated. I am using SQL Server 2008 R2.

 

[drlex]

Have a gander at Jeff Moden's article on SQLServerCentral, entitled "Group Islands of Contiguous Dates":

http://www.sqlservercentral.com/articles/T-SQL/71550/

soi là, soi carré

 

[SimonSellick]

That looks useful at first glance - thank you. I will check it further at work tomorrow.

 

[drlex]

You're welcome. By joining your example data to a numbers table to return all dates, I was able to use Jeff's code to produce the desired result.

soi là, soi carré

 

[SimonSellick]

That's very clever - thanks again!

Simon

 

[drlex]

Thanks for the accolade, although it really belongs to Jeff!

(I have a similar requirement in respect of attendance records, although complicated by the inclusion of half-days!)

soi là, soi carré

 

[markros]

Having Start and End dates as separate columns complicate the problem. One possibility is to first transpose both Start/End intervals into one column adding records in between using Numbers table. In this case we will be able to use Jeff' idea (I've seen the same idea discussed in other blogs as well, such as

http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

)

PluralSight Learning Library