First Businessday of a Month in a Year


create table holidays (holiday date, holidayName varchar(15))
Insert into holidays values('1/1/2014','New Year'),('9/1/2014','Labor Day')
-- Option 1 non recursive
create table holidays (holiday date, holidayName varchar(25))
Insert into holidays values('1/1/2014','New Year'),('9/1/2014','Labor Day'),('Feb 3, 2014', 'Another public holiday')
,('May 1, 2014', 'Goldenweek 1')
,('May 2, 2014', 'Goldenweek 2')
,('May 3, 2014', 'Goldenweek 3')
,('May 4, 2014', 'Goldenweek 4')
,('May 5, 2014', 'Goldenweek 5')
,('May 6, 2014', 'Goldenweek 6')
,('May 7, 2014', 'Goldenweek 7')
,('May 8, 2014', 'Goldenweek 8')
,('May 9, 2014', 'Goldenweek 9')

 ;with mycte as (
 Select dateadd(day, num, dateadd(month, number ,dateadd(year,datediff(year,0,getdate()),0)) ) dt  FROM master.dbo.spt_values   
  Cross apply (select number from master.dbo.spt_values WHERE type = 'P' AND number BETWEEN 0 AND 30) d(num) 
  WHERE type = 'P' AND number BETWEEN 0 AND 11  )

,mycte1 as (
 select dt  ,row_number() Over(Partition by Year(dt),  month(dt)  Order by  dt) rn  from mycte
  where DATEPART(weekday,dt) NOT IN (1,7)  and not exists(Select 1 FROM holidays WHERE holiday=dt)
 )

select Convert(varchar(10),dt,120) as FirstBusinessDay from mycte1
where rn=1 
Order by dt

 
 


 --Option 2 Recursive

 
  ;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),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) 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

 /*
FirstBusinessDay
2014-01-02
2014-02-04
2014-03-03
2014-04-01
2014-05-12
2014-06-02
2014-07-01
2014-08-01
2014-09-02
2014-10-01
2014-11-03
2014-12-01
 */


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s