How to find oldest Date Range without Gap

Question

I have a table DateRanges with the following format:

Id	FromDate	ToDate
1	2000-01-01	2001-12-31
2	2002-01-01	2003-12-31
3	2005-01-01	2006-12-31
4	2007-01-01	2008-12-31
5	2009-01-01	2010-12-31
6	2011-01-01	2012-12-31
7	2013-01-01	2014-12-31

I want to find the Oldest From Date that does not have a gap to the most recent Date. In the Example above the Oldest Date without a gap would be entry number 3. There can be a Gap allowed of max 30 Days to still count as not having a gap.

I tried to join the table on itself and check if an entry overlaps with another date to get the whole Range that they have together but the problem is that if there are more than 3 consecutive Dateranges it does no longer work.

My Instinct tells me to do this recursivly but i dont think that is possible in SQL.

EDIT: The Idea is to compare the different Entries in the Table with each other to get a list of all the Dates that follow each other. For Example Entry 1 ToDate (2001-12-31) with Entry 2 FromDate (2002-01-01) and because there is only a gap of 1 Day those dates are still connected

The Expected Result would be to Connect all of the Dateranges with each other that dont have a gap. For the Example above it would look like this:

FromDate	ToDate
2000-01-01	2003-12-31
2005-01-01	2014-12-31

Answer 1

use LAG() window function to find where the dates are not continuous. Cumulative sum() to form the grp. Finally a simple GROUP BY query will gives you the required result

with 
Dates as
(
    select Id, FromDate, ToDate, 
           f = case when dateadd(day, -1, FromDate) 
                    <>   lag(ToDate) over (order by FromDate)
                    then 1
                    else 0
                    end
    from   DateRanges
),
DateGroups as
(
    select Id, FromDate, ToDate, grp = sum(f) over(order by FromDate)
    from   Dates
)
select FromDate = min(FromDate), ToDate = max(ToDate)
from   DateGroups
group by grp