Consecutive days excluding weekends SQL 2008

Question

I am working on an out of office report, that as suggested shows when someone is off. I’ve been asked to include a returning to work date. So I need to write a query that checks the consecutive dates that the person is off. I’ve managed to find a query which is close, but it trips up due to weekends. So the default data is :

StaffCode   HolidayDate
J.Smith     2017-04-03 00:00:00.000
J.Smith     2017-04-04 00:00:00.000
J.Smith     2017-04-05 00:00:00.000
J.Smith     2017-04-06 00:00:00.000
J.Smith     2017-04-07 00:00:00.000
J.Smith     2017-04-10 00:00:00.000
J.Smith     2017-04-11 00:00:00.000
J.Smith     2017-04-12 00:00:00.000
J.Smith     2017-04-13 00:00:00.000
J.Smith     2017-04-18 00:00:00.000

When I run the query it returns

FirstDay            LastDay
2017-04-03          2017-04-07
2017-04-10          2017-04-13
2017-04-18          2017-04-18

But what I want is

firstDay                lastDay
2017-04-03              2017-04-13

This is the query im using, Is there a way of excluding weekends?

WITH t AS (
  SELECT HolidayDate d,ROW_NUMBER() OVER(ORDER BY HolidayDate) i
  FROM tblHoliday
  WHERE StaffCode = 'j.Smith'
        AND HolidayDate >= '27-Feb-2017'
  GROUP BY HolidayDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

Answer 1

This is where a Dates table comes into handy. There are many blog posts recommending adding a Dates table to a database, for situations where you need to tackle dates.

In order to create a Dates table you can use a recursive CTE:

;with dates as (
select cast('19000101' as datetime) [current_date]
union all
select dateadd(day, 1, current_date)
from dates
where current_date <= cast('20991231' as datetime)
)
select
    [current_date]
    , NULL [holiday]
    , case datename(weekday, prev_date) 
        when 'Saturday'
            then 1
        when 'Sunday'
            then 1
        else 0
    end     [weekend]
into Dates
from dates
option(maxrecursion 0);

As you can see I added two extra columns which might be helpful in the future. One could contain a boolean to point if a specific day is a holiday/national day off - [holiday] column.

The 3rd column also contains a boolean to point if a day is a weekend day or not.

So, using the Dates table, in a single-query you could do something like this (it's not pretty):

-- I'll also create the sample data and store it into a timeOff table
create table timeOff (staffCode varchar(25), holidayDate datetime);

insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-03T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-04T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-05T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-06T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-07T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-10T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-11T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-12T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-13T00:00:00.000' AS DateTime));
insert [dbo].[timeOff] ([staffCode], [holidayDate]) values (N'J.Smith', CAST(N'2017-04-18T00:00:00.000' AS DateTime));

-- And now for the query itself (it's not pretty but I came up with it in a short time)

select 
    [staffCode]
    , min([current_date])   [firstDay]
    , max([current_date])   [lastDay]
from
    (select d.[current_date]
        , t.staffCode
        , min(case when d.weekend = 0 and t.holidayDate is null then d.[current_date] else null end) over () [last_date]
    from 
        (select [staffCode]
            , min(holidayDate) [mindate]
            , max(holidayDate) [maxdate]
        from timeOff t 
        where staffcode = 'J.Smith'
        group by staffCode) r
    inner join dates d on d.[current_date] between mindate and maxdate
    left join timeOff t on d.[current_date] = t.holidayDate ) r
where [current_date] < [last_date]
    and staffCode is not null
group by staffCode;