0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

created may 20 11 Andomar