SQLTeam.com | Weblogs | Forums

Query Help


#1

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


#2

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.


#3

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.


#4

India


#5

In British English that has to be Reverse Polish, no? :slight_smile:


#6

Maybe just "Damn Yankees!" :laughing: