recursive CTE to get first business day excluding public holidays

I like the functions I built to get this done:

create function NthDayOfMonth (@year int, @month smallint, @weekday varchar(15), @nth smallint)
 returns datetime
 as
 begin
 declare @the_date datetime, @c_date datetime, @cth smallint
 set @cth = 0
 set @c_date = convert(varchar,@year)+'-'+convert(varchar,@month)+'-01'
 
  while month(@c_date) = @month
  begin
  if datename(weekday,@c_date) = @weekday set @cth = @cth + 1
  if @cth = @nth and datename(weekday,@c_date) = @weekday set @the_date = @c_date
  set @c_date = dateadd(day,1,@c_date)
  end

return @the_date 
end

GO

create function Holidays(@year int)
returns @table table
(
 date date,
 type varchar(10),
 name varchar(25)
)
as
begin
insert into @table
select convert(datetime,convert(varchar,@year)+'-01-01') as date,'Holiday' as type                                    ,'New Years Day' as name UNION ALL
select dbo.NthDayOfMonth(@year,2, 'Monday',3),'Holiday','Family Day'      UNION ALL
select dateadd(d,0-case when datepart(weekday,convert(varchar,@year)+'-05-25') in (1,2) then 5+datepart(weekday,convert(varchar,@year)+'-05-25') else datepart(weekday,convert(varchar,@year)+'-05-25')-1 end, convert(varchar,@year)+'-05-25') ,'Holiday','Victoria Day'    UNION ALL
select convert(varchar,@year)+'-07-01'                         ,'Holiday','Canada Day'      UNION ALL
select dbo.NthDayOfMonth(@year,8, 'Monday',1),'Holiday','Civic Holiday'   UNION ALL
select dbo.NthDayOfMonth(@year,9, 'Monday',1),'Holiday','Labour Day'      UNION ALL
select dbo.NthDayofMonth(@year,10,'Monday',2),'Holiday','Thanksgiving'    UNION ALL
select convert(varchar,@year)+'-11-11'                         ,'Holiday','Rememberance Day'UNION ALL
select convert(varchar,@year)+'-12-25'                         ,'Holiday','Christmas Day'   UNION ALL
select convert(varchar,@year)+'-12-26'                         ,'Holiday','Boxing Day' UNION ALL
SELECT CONVERT(DATE,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0))),'Holiday','Easter Sunday' UNION ALL
SELECT DATEADD(DAY,-2,CONVERT(DATE,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0)))),'Holiday','Good Friday' UNION ALL
SELECT DATEADD(DAY,1,CONVERT(DATE,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0)))),'Holiday','Easter Monday'

update @table
set date = 
case when MONTH(date) = 12 AND name != 'Boxing Day' and datepart(weekday,date) = 7 then dateadd(day,2,date)
     when MONTH(date) = 12 AND name != 'Boxing Day' and datepart(weekday,date) = 1 then dateadd(day,1,date)
         when MONTH(date) = 12 AND name = 'Boxing Day' and datepart(weekday,date) = 7 then dateadd(day,2,date)
         when MONTH(date) = 12 AND name = 'Boxing Day' and datepart(weekday,date) = 1 then dateadd(day,2,date)
         when MONTH(date) = 12 AND name = 'Boxing Day' and datepart(weekday,date) = 2 then dateadd(day,1,date)
         else date
end 


return 

end

GO

create function Dates(@date datetime)
returns @table table
(
now datetime,
today datetime,
Month_start datetime,
Month_end datetime,
Prev_Month_Start datetime,
Prev_Month_End datetime,
Week_Start datetime,
Week_End datetime,
Prev_Week_Start datetime,
Prev_Week_End datetime,
Quarter_Start datetime,
Quarter_End datetime,
Prev_Quarter_Start datetime,
Prev_Quarter_End datetime,
Year_Start datetime,
Year_End datetime,
Prev_Year_Start datetime,
Prev_Year_End datetime,
Month_End_TS datetime,
Prev_Month_End_TS datetime,
Week_End_TS datetime,
Prev_Week_End_TS datetime,
Quarter_End_TS datetime,
Prev_Quarter_End_TS datetime,
Year_End_TS datetime,
Prev_Year_End_TS datetime,
Year smallint,
Month smallint,
Day smallint,
Month_Name varchar(15),
Day_Name varchar(15),
WD SMALLINT,
Week INT,
isHoliday bit
)
as

begin 

if @date IS NULL set @date = getdate()

insert into @table

select

@date as now, 
convert(datetime,convert(varchar,@date,101)) as today,

dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101))) as Month_Start,
dateadd(day,-1,dateadd(month,1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101))))) as Month_end,
dateadd(month,-1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101)))) as Prev_Month_start,
dateadd(day,-1-day(@date)+1,convert(datetime,convert(varchar,@date,101))) as Prev_Month_End,

dateadd(day,1-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Week_Start,
dateadd(day,7-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Week_End,
dateadd(day,-6-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Prev_Week_Start,
dateadd(day,0-datepart(dw,@date),convert(datetime,convert(varchar,@date,101))) as Prev_Week_End,

convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01') as quarter_start,
dateadd(day,-1,dateadd(quarter,1,convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01'))) as quarter_end,
convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01') as prev_quarter_start,
dateadd(day,-1,dateadd(quarter,1,convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01'))) as prev_quarter_end,

dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))) as Year_Start,
dateadd(year,1,dateadd(day,0-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Year_End,

dateadd(year,-1,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Prev_Year_Start,
dateadd(year,-1,dateadd(year,1,dateadd(day,0-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))))) as Prev_Year_End,

dateadd(ms,-3,dateadd(day,0,dateadd(month,1,dateadd(day,0-day(@date)+1,convert(datetime,convert(varchar,@date,101)))))) as Month_End_Ts,
dateadd(ms,-3,dateadd(day,-1-day(@date)+2,convert(datetime,convert(varchar,@date,101)))) as Prev_Month_End_TS,
dateadd(ms,-3,dateadd(day,8-datepart(dw,@date),convert(datetime,convert(varchar,@date,101)))) as Week_End_TS,
dateadd(ms,-3,dateadd(day,1-datepart(dw,@date),convert(datetime,convert(varchar,@date,101)))) as Prev_Week_End_TS,

dateadd(ms,-3,dateadd(day,0,dateadd(quarter,1,convert(datetime,convert(varchar,year(@date)) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,@date)-1)*3)+1),2)+'-01')))) as quarter_end_TS,
dateadd(ms,-3,dateadd(day,0,dateadd(quarter,1,convert(datetime,convert(varchar,year(dateadd(quarter,-1,@date))) +'-'+ right('0'+convert(varchar,((datepart(QUARTER,dateadd(quarter,-1,@date))-1)*3)+1),2)+'-01')))) as prev_quarter_end_TS,


dateadd(ms,-3,dateadd(year,1,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101))))) as Year_End_TS,
dateadd(ms,-3,dateadd(day,1-day(@date),dateadd(month,1-month(@date),convert(varchar,@date,101)))) as Prev_Year_End_TS,

Year(@date) as Year,
Month(@date) as Month,
Day(@Date) as Day,
datename(month,@Date) as Month_Name,
datename(WEEKDAY,@date) as Day_Name,
datepart(weekday,@date) as WD,
DATEPART(WEEK,@date) AS Week,
COALESCE((SELECT 1 
   FROM dbo.Holidays(YEAR(@date)) h
  WHERE h.date = @date),0) AS isHoliday


return 

end

go

The holidays function is set up for Saskatchewan right now, but its a small matter to adjust it to your region.

You can then do this:

DECLARE @calendar TABLE (date DATE)
WHILE (SELECT COUNT(*) FROM @calendar) < 365
BEGIN
INSERT INTO @calendar (date)
VALUES  (DATEADD(day,1,(SELECT COALESCE(MAX(date),CURRENT_TIMESTAMP) FROM @calendar)))
END

SELECT MONTH(date) AS month, MIN(date) AS fbd
  FROM @calendar c
    CROSS APPLY dbo.Dates(c.date)
 WHERE DATEPART(DAY,c.date) NOT IN (1,7) AND isHoliday = 0
 GROUP BY MONTH(date)