-- example code for calculating working days

declare @StartDate date = '2000-01-01';

set DATEFIRST 1;

WITH

bh as

(

SELECT '2016-01-01' BankHoliday UNION ALL

SELECT '25-03-2016' BankHoliday UNION ALL

SELECT '28-03-2016' BankHoliday UNION ALL

SELECT '02-05-2016' BankHoliday UNION ALL

SELECT '30-05-2016' BankHoliday UNION ALL

SELECT '29-05-2016' BankHoliday UNION ALL

SELECT '28-08-2016' BankHoliday UNION ALL

SELECT '25-12-2016' BankHoliday UNION ALL

SELECT '26-12-2016' BankHoliday UNION ALL

SELECT '02-01-2017' BankHoliday UNION ALL

SELECT '14-04-2017' BankHoliday UNION ALL

SELECT '17-04-2017' BankHoliday UNION ALL

SELECT '01-05-2017' BankHoliday UNION ALL

SELECT '29-05-2017' BankHoliday UNION ALL

SELECT '28-08-2017' BankHoliday UNION ALL

SELECT '25-12-2017' BankHoliday UNION ALL

SELECT '26-12-2017' BankHoliday

),

Numbers AS

(

SELECT n = 1

UNION ALL

SELECT n + 1

FROM Numbers

WHERE n+1 <= 10000

),

DateRange as

(

SELECT

DATEADD(dd,n,@StartDate) ActualDate,

datepart(dw,DATEADD(d,n,@StartDate)) dow,

bh.BankHoliday

FROM Numbers

left join bh

on bh.BankHoliday =DATEADD(dd,n,@StartDate)

),

WorkingDays as

(

SELECT

ActualDate,

row_number() over(order by ActualDate) WorkingDays

from DateRange

where dow not in (6,7) and BankHoliday is null

)

select

a.ActualDate,

a.dow,

a.BankHoliday,

wd.WorkingDays

from DateRange a

left join WorkingDays wd

on a.ActualDate=wd.ActualDate

OPTION (MAXRECURSION 10000)