declare @YourTable table (StartDate date, EndDate date) ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- Enter Query Title
-- Enter Query Description

declare @YourTable table (StartDate date, EndDate date)
insert @YourTable
select '1982.03.02', '1982.09.30'
union all select '1982.10.01', '1985.01.17'
union all select '1985.06.26', '1985.07.26'
union all select '1985.07.30', '1991.12.31'
union all select '1992.01.01', '1995.12.31'
union all select '1996.01.01', '2004.05.31'
union all select '2004.06.01', '2006.01.31'
union all select '2006.02.01', '2011.05.20'  

; with heads as
    (
    select  StartDate
    ,    EndDate
    ,    row_number() over (order by StartDate) as rn
    from  @YourTable h
    where  not exists
        (
        select  *
        from  @YourTable next
        where  next.EndDate = dateadd(day, -1, h.StartDate)
        )
    )
select  heads.StartDate
,    (
    select  top 1 EndDate
    from  @YourTable
    where  EndDate < COALESCE(
        (
        select  StartDate
        from  heads h2
        where  heads.rn + 1 = h2.rn
        ), '9999-01-01')
    order by
        EndDate desc
    )
from  heads

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached