Need help creating SQL query to roll up dates which include date gaps

[mjmdgov]

Hi folks,

I've been tasked with creating a query that consolidates dates
that are consecutive and contains non-consecutive dates.

For example:

1000 04-01-2001 03-31-2002
1000 04-01-2002 03-31-2003
1000 04-01-2002 08-17-2002
1000 01-01-2008 01-03-2008
1000 01-01-2008 12-31-2008

needs to look like this:


1000 04-01-2001 08-17-2002
1000 01-01-2008 12-31-2008

My company provides services to clients based on a 12 month period.

There are times when we terminate services before 12 months period is up.

We also allow clients a free trial of our services which can be made up of periods that can be from 1 month or 3 months.

I dont where to begin with this task.

Can any one assist?

Thanks In Advance,

Mike James
Maryland State Government

 

[gmmastros]

This type of problem has a name. I suggest you do a google search on "SQL Server Date Range Islands".

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Mike,

To add to George's suggestion, here is an article I know that will help

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

PluralSight Learning Library