recursive CTE to get first business day excluding public holidays

create table holidays (holiday date, holidayName varchar(15))
Insert into holidays values('1/1/2014','New Year'),('9/1/2014','Labor Day')

--;with mycte as (Select dateadd(day, n, Dateadd(month, val, dateadd(year,datediff(year,0,getdate()),0))) dt 
--from  (Values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d(val)   
--Cross apply ( values (0),(1),(2)) num(n)
--)

--,mycte1 as ( Select  dt , row_number() Over(Partition by month(dt),Year(dt) Order by  dt) rn from mycte m
--WHERE DATEPART(weekday,dt) NOT IN (1,7) 
-- and not exists(Select 1 FROM holidays WHERE holiday=m.dt)
-- )
-- Select  Cast(dt as Date) as FirstBusinessDay from mycte1
-- WHERE rn=1



 ;with cte as (
 Select 1 as i,   dateadd(year,datediff(year,0,getdate()),0) dt 
 Union ALL
 Select c.i+1 as i,  dateadd(month, 1, c.dt) dt From cte c
 WHERE i<12
 ) 
 
 ,mycte1 as (
 select dateadd(Day, n, dt) dt
 ,row_number() Over(Partition by month(dt),Year(dt) Order by  dt) rn from    cte m
 Cross apply (values(0),(1),(2)) d(n)
  WHERE DATEPART(weekday,dateadd(Day, n, dt)) NOT IN (1,7) 
  and not exists(Select 1 FROM holidays WHERE holiday=dateadd(Day, n, m.dt))
 )
Select Convert(varchar(10),dt,120)  as FirstBusinessDay from mycte1 m
WHERE rn=1
Order by dt
 
drop table holidays