Hai,
I would like to find the Starting & Ending date of continuous holidays from the data below
Table Content
LeaveDate
2016-01-03
2016-01-10
2016-01-15
2016-01-16
2016-01-17
2016-01-24
2016-01-26
2016-01-30
2016-01-31
Output
StartDt,EndDt
2016-01-15,2016-01-17
2016-01-30,2016-01-31
This is possible not the most effective query, but it gets the job done:
with cte1(leavedate,rn)
as (select leavedate
,row_number() over(order by leavedate)
from yourtable
)
,cte2(leavedate,rn)
as (select a.leavedate
,row_number() over(order by a.leavedate)
from cte1 as a
left outer join cte1 as b
on b.rn=a.rn-1
and b.leavedate=dateadd(day,-1,a.leavedate)
inner join cte1 as c
on c.rn=a.rn+1
and c.leavedate=dateadd(day,1,a.leavedate)
where b.leavedate is null
)
,cte3(leavedate,rn)
as (select a.leavedate
,row_number() over(order by a.leavedate)
from cte1 as a
inner join cte1 as b
on b.rn=a.rn-1
and b.leavedate=dateadd(day,-1,a.leavedate)
left outer join cte1 as c
on c.rn=a.rn+1
and c.leavedate=dateadd(day,1,a.leavedate)
where c.leavedate is null
)
select a.leavedate as StartDt
,b.leavedate as EndDt
from cte2 as a
left outer join cte3 as b
on b.rn=a.rn
;
Ps.: I'm very currious of which country you live in, that has that many holidays in one month.
I'm not claiming this is any more performant; just another approach:[code]declare @Holiday table (
LeaveDate date
)
insert into @Holiday(LeaveDate)
values
('2016-01-03'),
('2016-01-10'),
('2016-01-15'),
('2016-01-16'),
('2016-01-17'),
('2016-01-24'),
('2016-01-26'),
('2016-01-30'),
('2016-01-31')
;with StartDates
as (
select
a.LeaveDate
from
@Holiday a
where
not exists ( -- Not on holiday yesterday
select *
from @Holiday a1
where a1.LeaveDate = DATEADD(Day, -1, a.LeaveDate)
)
and exists ( -- Are on holiday tomorrow
select *
from @Holiday a2
where a2.LeaveDate = DATEADD(Day, 1, a.LeaveDate)
)
),
EndDates
as (
select
a.leaveDate
from
@Holiday a
where
exists ( -- Are on holiday yesterday
select *
from @Holiday a1
where a1.LeaveDate = DATEADD(Day, -1, a.LeaveDate)
)
and not exists ( -- Not on holiday tomorrow
select *
from @Holiday a2
where a2.LeaveDate = DATEADD(Day, 1, a.LeaveDate)
)
)
select
s.LeaveDate StartDate,
e.LeaveDate EndDate
from
StartDates s
inner join
EndDates e
on s.LeaveDate < e.LeaveDate
and not exists ( -- Finds the minimum end date
select *
from EndDates e1
where s.LeaveDate < e1.LeaveDate and e1.LeaveDate < e.LeaveDate
)
order by
StartDate,
EndDate[/code]In "British" English Holiday = Vacation.
India
In British English that has to be Reverse Polish, no? 
Maybe just "Damn Yankees!" 