Finding gaps in dates
We have a table that lists start and end dates for "service time". I would like a query that can search through all of the rows and identify any breaks in service based on gaps in the dates.
Data:
Start End
1/1/2000 2/1/2001
2/2/2001 4/1/2001
4/1/2004 6/2/2006
6/3/2006 9/1/2010
8/1/2011 9/1/2012
Desired result:
1/1/2001 - 4/1/2001 //The first two ranges collapsed because there was no real break in service
4/1/2004 - 9/1/2010 // The 3rd and 4th rows collapsed because no real break in service
8/1/2011 - 9/1/2012
This probably more easily done in app logic or stored proc, just wondering if there is any SQL that could get me close.
_mike