Generate list of working day after third friday of each month in given year

Transact-SQL
1

I have table workingDates that has 3 columns

  1. wd:(type int ) This column has all working dates in int format for a year weekends and holidays are not included.
  2. fulldate (datatype date): same as wd but in date format.
  3. dayname : this contains weekdayname like monday,tuesday.

I want to generate list of two dates for each month as per following rule.

  1. second friday of each month (if second friday is holiday for that month then that date would not be there workingDates.wd in this case i need previous working i,e thrusday or wednesday whatever is working day availble in table )

  2. Monday after third friday for each month ( in this case if monday is holiday i need next working day i.e tuesday or wednesday whatever is working day availble in table )

I need your help to create the query in this scenario.

output expected :

year month rule_1_dates rule_2_dates
2019 Jan 20190111 20190121
and so on.......

base table workingDates data :

declare @workingDates table (wd int , fulldate date , [dayname] varchar(30))
insert into @workingDates values (20190102,'2019-01-02','Wednesday')
insert into @workingDates values (20190103,'2019-01-03','Thursday')
insert into @workingDates values (20190104,'2019-01-04','Friday')
insert into @workingDates values (20190107,'2019-01-07','Monday')
insert into @workingDates values (20190108,'2019-01-08','Tuesday')
insert into @workingDates values (20190109,'2019-01-09','Wednesday')
insert into @workingDates values (20190110,'2019-01-10','Thursday')
insert into @workingDates values (20190111,'2019-01-11','Friday')
insert into @workingDates values (20190114,'2019-01-14','Monday')
insert into @workingDates values (20190115,'2019-01-15','Tuesday')
insert into @workingDates values (20190116,'2019-01-16','Wednesday')
insert into @workingDates values (20190117,'2019-01-17','Thursday')
insert into @workingDates values (20190118,'2019-01-18','Friday')
insert into @workingDates values (20190121,'2019-01-21','Monday')
insert into @workingDates values (20190122,'2019-01-22','Tuesday')
insert into @workingDates values (20190123,'2019-01-23','Wednesday')
insert into @workingDates values (20190124,'2019-01-24','Thursday')
insert into @workingDates values (20190125,'2019-01-25','Friday')
insert into @workingDates values (20190128,'2019-01-28','Monday')
insert into @workingDates values (20190129,'2019-01-29','Tuesday')
insert into @workingDates values (20190130,'2019-01-30','Wednesday')
insert into @workingDates values (20190131,'2019-01-31','Thursday')
insert into @workingDates values (20190201,'2019-02-01','Friday')
insert into @workingDates values (20190204,'2019-02-04','Monday')
insert into @workingDates values (20190205,'2019-02-05','Tuesday')
insert into @workingDates values (20190206,'2019-02-06','Wednesday')
insert into @workingDates values (20190207,'2019-02-07','Thursday')
insert into @workingDates values (20190208,'2019-02-08','Friday')
insert into @workingDates values (20190211,'2019-02-11','Monday')
insert into @workingDates values (20190212,'2019-02-12','Tuesday')
insert into @workingDates values (20190213,'2019-02-13','Wednesday')
insert into @workingDates values (20190214,'2019-02-14','Thursday')
insert into @workingDates values (20190215,'2019-02-15','Friday')
insert into @workingDates values (20190218,'2019-02-18','Monday')
insert into @workingDates values (20190219,'2019-02-19','Tuesday')
insert into @workingDates values (20190220,'2019-02-20','Wednesday')
insert into @workingDates values (20190221,'2019-02-21','Thursday')
insert into @workingDates values (20190222,'2019-02-22','Friday')
insert into @workingDates values (20190225,'2019-02-25','Monday')
insert into @workingDates values (20190226,'2019-02-26','Tuesday')
insert into @workingDates values (20190227,'2019-02-27','Wednesday')
insert into @workingDates values (20190228,'2019-02-28','Thursday')
insert into @workingDates values (20190301,'2019-03-01','Friday')
insert into @workingDates values (20190304,'2019-03-04','Monday')
insert into @workingDates values (20190305,'2019-03-05','Tuesday')
insert into @workingDates values (20190306,'2019-03-06','Wednesday')
insert into @workingDates values (20190307,'2019-03-07','Thursday')
insert into @workingDates values (20190308,'2019-03-08','Friday')
insert into @workingDates values (20190311,'2019-03-11','Monday')
insert into @workingDates values (20190312,'2019-03-12','Tuesday')
insert into @workingDates values (20190313,'2019-03-13','Wednesday')
insert into @workingDates values (20190314,'2019-03-14','Thursday')
insert into @workingDates values (20190315,'2019-03-15','Friday')
insert into @workingDates values (20190318,'2019-03-18','Monday')
insert into @workingDates values (20190319,'2019-03-19','Tuesday')
insert into @workingDates values (20190320,'2019-03-20','Wednesday')
insert into @workingDates values (20190321,'2019-03-21','Thursday')
insert into @workingDates values (20190322,'2019-03-22','Friday')
insert into @workingDates values (20190325,'2019-03-25','Monday')
insert into @workingDates values (20190326,'2019-03-26','Tuesday')
insert into @workingDates values (20190327,'2019-03-27','Wednesday')
insert into @workingDates values (20190328,'2019-03-28','Thursday')
insert into @workingDates values (20190329,'2019-03-29','Friday')
insert into @workingDates values (20190401,'2019-04-01','Monday')
insert into @workingDates values (20190402,'2019-04-02','Tuesday')
insert into @workingDates values (20190403,'2019-04-03','Wednesday')
insert into @workingDates values (20190404,'2019-04-04','Thursday')
insert into @workingDates values (20190405,'2019-04-05','Friday')
insert into @workingDates values (20190408,'2019-04-08','Monday')
insert into @workingDates values (20190409,'2019-04-09','Tuesday')
insert into @workingDates values (20190410,'2019-04-10','Wednesday')
insert into @workingDates values (20190411,'2019-04-11','Thursday')
insert into @workingDates values (20190412,'2019-04-12','Friday')
insert into @workingDates values (20190415,'2019-04-15','Monday')
insert into @workingDates values (20190416,'2019-04-16','Tuesday')
insert into @workingDates values (20190417,'2019-04-17','Wednesday')
insert into @workingDates values (20190418,'2019-04-18','Thursday')
insert into @workingDates values (20190423,'2019-04-23','Tuesday')
insert into @workingDates values (20190424,'2019-04-24','Wednesday')
insert into @workingDates values (20190425,'2019-04-25','Thursday')
insert into @workingDates values (20190426,'2019-04-26','Friday')
insert into @workingDates values (20190429,'2019-04-29','Monday')
insert into @workingDates values (20190430,'2019-04-30','Tuesday')
insert into @workingDates values (20190501,'2019-05-01','Wednesday')
insert into @workingDates values (20190502,'2019-05-02','Thursday')
insert into @workingDates values (20190503,'2019-05-03','Friday')
insert into @workingDates values (20190506,'2019-05-06','Monday')
insert into @workingDates values (20190507,'2019-05-07','Tuesday')
insert into @workingDates values (20190508,'2019-05-08','Wednesday')
insert into @workingDates values (20190509,'2019-05-09','Thursday')
insert into @workingDates values (20190510,'2019-05-10','Friday')
insert into @workingDates values (20190513,'2019-05-13','Monday')
insert into @workingDates values (20190514,'2019-05-14','Tuesday')
insert into @workingDates values (20190515,'2019-05-15','Wednesday')
insert into @workingDates values (20190516,'2019-05-16','Thursday')
insert into @workingDates values (20190517,'2019-05-17','Friday')
insert into @workingDates values (20190520,'2019-05-20','Monday')
insert into @workingDates values (20190521,'2019-05-21','Tuesday')
insert into @workingDates values (20190522,'2019-05-22','Wednesday')
insert into @workingDates values (20190523,'2019-05-23','Thursday')
insert into @workingDates values (20190524,'2019-05-24','Friday')
insert into @workingDates values (20190527,'2019-05-27','Monday')
insert into @workingDates values (20190528,'2019-05-28','Tuesday')
insert into @workingDates values (20190529,'2019-05-29','Wednesday')
insert into @workingDates values (20190530,'2019-05-30','Thursday')
insert into @workingDates values (20190531,'2019-05-31','Friday')
insert into @workingDates values (20190603,'2019-06-03','Monday')
insert into @workingDates values (20190604,'2019-06-04','Tuesday')
insert into @workingDates values (20190605,'2019-06-05','Wednesday')
insert into @workingDates values (20190606,'2019-06-06','Thursday')
insert into @workingDates values (20190607,'2019-06-07','Friday')
insert into @workingDates values (20190610,'2019-06-10','Monday')
insert into @workingDates values (20190611,'2019-06-11','Tuesday')
insert into @workingDates values (20190612,'2019-06-12','Wednesday')
insert into @workingDates values (20190613,'2019-06-13','Thursday')
insert into @workingDates values (20190614,'2019-06-14','Friday')
insert into @workingDates values (20190617,'2019-06-17','Monday')
insert into @workingDates values (20190618,'2019-06-18','Tuesday')
insert into @workingDates values (20190619,'2019-06-19','Wednesday')
insert into @workingDates values (20190620,'2019-06-20','Thursday')
insert into @workingDates values (20190621,'2019-06-21','Friday')
insert into @workingDates values (20190624,'2019-06-24','Monday')
insert into @workingDates values (20190625,'2019-06-25','Tuesday')
insert into @workingDates values (20190626,'2019-06-26','Wednesday')
insert into @workingDates values (20190627,'2019-06-27','Thursday')
insert into @workingDates values (20190628,'2019-06-28','Friday')
insert into @workingDates values (20190701,'2019-07-01','Monday')
insert into @workingDates values (20190702,'2019-07-02','Tuesday')
insert into @workingDates values (20190703,'2019-07-03','Wednesday')
insert into @workingDates values (20190704,'2019-07-04','Thursday')
insert into @workingDates values (20190705,'2019-07-05','Friday')
insert into @workingDates values (20190708,'2019-07-08','Monday')
insert into @workingDates values (20190709,'2019-07-09','Tuesday')
insert into @workingDates values (20190710,'2019-07-10','Wednesday')
insert into @workingDates values (20190711,'2019-07-11','Thursday')
insert into @workingDates values (20190712,'2019-07-12','Friday')
insert into @workingDates values (20190715,'2019-07-15','Monday')
insert into @workingDates values (20190716,'2019-07-16','Tuesday')
insert into @workingDates values (20190717,'2019-07-17','Wednesday')
insert into @workingDates values (20190718,'2019-07-18','Thursday')
insert into @workingDates values (20190719,'2019-07-19','Friday')
insert into @workingDates values (20190722,'2019-07-22','Monday')
insert into @workingDates values (20190723,'2019-07-23','Tuesday')
insert into @workingDates values (20190724,'2019-07-24','Wednesday')
insert into @workingDates values (20190725,'2019-07-25','Thursday')
insert into @workingDates values (20190726,'2019-07-26','Friday')
insert into @workingDates values (20190729,'2019-07-29','Monday')
insert into @workingDates values (20190730,'2019-07-30','Tuesday')
insert into @workingDates values (20190731,'2019-07-31','Wednesday')
insert into @workingDates values (20190801,'2019-08-01','Thursday')
insert into @workingDates values (20190802,'2019-08-02','Friday')
insert into @workingDates values (20190805,'2019-08-05','Monday')
insert into @workingDates values (20190806,'2019-08-06','Tuesday')
insert into @workingDates values (20190807,'2019-08-07','Wednesday')
insert into @workingDates values (20190808,'2019-08-08','Thursday')
insert into @workingDates values (20190809,'2019-08-09','Friday')
insert into @workingDates values (20190812,'2019-08-12','Monday')
insert into @workingDates values (20190813,'2019-08-13','Tuesday')
insert into @workingDates values (20190814,'2019-08-14','Wednesday')
insert into @workingDates values (20190815,'2019-08-15','Thursday')
insert into @workingDates values (20190816,'2019-08-16','Friday')
insert into @workingDates values (20190819,'2019-08-19','Monday')
insert into @workingDates values (20190820,'2019-08-20','Tuesday')
insert into @workingDates values (20190821,'2019-08-21','Wednesday')
insert into @workingDates values (20190822,'2019-08-22','Thursday')
insert into @workingDates values (20190823,'2019-08-23','Friday')
insert into @workingDates values (20190826,'2019-08-26','Monday')
insert into @workingDates values (20190827,'2019-08-27','Tuesday')
insert into @workingDates values (20190828,'2019-08-28','Wednesday')
insert into @workingDates values (20190829,'2019-08-29','Thursday')
insert into @workingDates values (20190830,'2019-08-30','Friday')
insert into @workingDates values (20190902,'2019-09-02','Monday')
insert into @workingDates values (20190903,'2019-09-03','Tuesday')
insert into @workingDates values (20190904,'2019-09-04','Wednesday')
insert into @workingDates values (20190905,'2019-09-05','Thursday')
insert into @workingDates values (20190906,'2019-09-06','Friday')
insert into @workingDates values (20190909,'2019-09-09','Monday')
insert into @workingDates values (20190910,'2019-09-10','Tuesday')
insert into @workingDates values (20190911,'2019-09-11','Wednesday')
insert into @workingDates values (20190912,'2019-09-12','Thursday')
insert into @workingDates values (20190913,'2019-09-13','Friday')
insert into @workingDates values (20190916,'2019-09-16','Monday')
insert into @workingDates values (20190917,'2019-09-17','Tuesday')
insert into @workingDates values (20190918,'2019-09-18','Wednesday')
insert into @workingDates values (20190919,'2019-09-19','Thursday')
insert into @workingDates values (20190920,'2019-09-20','Friday')
insert into @workingDates values (20190923,'2019-09-23','Monday')
insert into @workingDates values (20190924,'2019-09-24','Tuesday')
insert into @workingDates values (20190925,'2019-09-25','Wednesday')
insert into @workingDates values (20190926,'2019-09-26','Thursday')
insert into @workingDates values (20190927,'2019-09-27','Friday')
insert into @workingDates values (20190930,'2019-09-30','Monday')
insert into @workingDates values (20191001,'2019-10-01','Tuesday')
insert into @workingDates values (20191002,'2019-10-02','Wednesday')
insert into @workingDates values (20191003,'2019-10-03','Thursday')
insert into @workingDates values (20191004,'2019-10-04','Friday')
insert into @workingDates values (20191007,'2019-10-07','Monday')
insert into @workingDates values (20191008,'2019-10-08','Tuesday')
insert into @workingDates values (20191009,'2019-10-09','Wednesday')
insert into @workingDates values (20191010,'2019-10-10','Thursday')
insert into @workingDates values (20191011,'2019-10-11','Friday')
insert into @workingDates values (20191014,'2019-10-14','Monday')
insert into @workingDates values (20191015,'2019-10-15','Tuesday')
insert into @workingDates values (20191016,'2019-10-16','Wednesday')
insert into @workingDates values (20191017,'2019-10-17','Thursday')
insert into @workingDates values (20191018,'2019-10-18','Friday')
insert into @workingDates values (20191021,'2019-10-21','Monday')
insert into @workingDates values (20191022,'2019-10-22','Tuesday')
insert into @workingDates values (20191023,'2019-10-23','Wednesday')
insert into @workingDates values (20191024,'2019-10-24','Thursday')
insert into @workingDates values (20191025,'2019-10-25','Friday')
insert into @workingDates values (20191028,'2019-10-28','Monday')
insert into @workingDates values (20191029,'2019-10-29','Tuesday')
insert into @workingDates values (20191030,'2019-10-30','Wednesday')
insert into @workingDates values (20191031,'2019-10-31','Thursday')
insert into @workingDates values (20191101,'2019-11-01','Friday')
insert into @workingDates values (20191104,'2019-11-04','Monday')
insert into @workingDates values (20191105,'2019-11-05','Tuesday')
insert into @workingDates values (20191106,'2019-11-06','Wednesday')
insert into @workingDates values (20191107,'2019-11-07','Thursday')
insert into @workingDates values (20191108,'2019-11-08','Friday')
insert into @workingDates values (20191111,'2019-11-11','Monday')
insert into @workingDates values (20191112,'2019-11-12','Tuesday')
insert into @workingDates values (20191113,'2019-11-13','Wednesday')
insert into @workingDates values (20191114,'2019-11-14','Thursday')
insert into @workingDates values (20191115,'2019-11-15','Friday')
insert into @workingDates values (20191118,'2019-11-18','Monday')
insert into @workingDates values (20191119,'2019-11-19','Tuesday')
insert into @workingDates values (20191120,'2019-11-20','Wednesday')
insert into @workingDates values (20191121,'2019-11-21','Thursday')
insert into @workingDates values (20191122,'2019-11-22','Friday')
insert into @workingDates values (20191125,'2019-11-25','Monday')
insert into @workingDates values (20191126,'2019-11-26','Tuesday')
insert into @workingDates values (20191127,'2019-11-27','Wednesday')
insert into @workingDates values (20191128,'2019-11-28','Thursday')
insert into @workingDates values (20191129,'2019-11-29','Friday')
insert into @workingDates values (20191202,'2019-12-02','Monday')
insert into @workingDates values (20191203,'2019-12-03','Tuesday')
insert into @workingDates values (20191204,'2019-12-04','Wednesday')
insert into @workingDates values (20191205,'2019-12-05','Thursday')
insert into @workingDates values (20191206,'2019-12-06','Friday')
insert into @workingDates values (20191209,'2019-12-09','Monday')
insert into @workingDates values (20191210,'2019-12-10','Tuesday')
insert into @workingDates values (20191211,'2019-12-11','Wednesday')
insert into @workingDates values (20191216,'2019-12-16','Monday')
insert into @workingDates values (20191217,'2019-12-17','Tuesday')
insert into @workingDates values (20191218,'2019-12-18','Wednesday')
insert into @workingDates values (20191219,'2019-12-19','Thursday')
insert into @workingDates values (20191220,'2019-12-20','Friday')
insert into @workingDates values (20191224,'2019-12-24','Tuesday')
insert into @workingDates values (20191227,'2019-12-27','Friday')
insert into @workingDates values (20191230,'2019-12-30','Monday')
insert into @workingDates values (20191231,'2019-12-31','Tuesday')

2

This is one way of doing it:

declare @year int=2019;

with cte1(dt,yyyymm,wd)
  as (select dt
            ,datepart(year,dt)*100+datepart(month,dt) as yyyymm
            ,datediff(day,0,dt)%7 as wd
        from (select dateadd(day,row_number() over(order by (select null))-1,cast(cast(@year as char(4))+'-01-01' as date)) as dt
                from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte1(n)
                     cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte2(n)
                     cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte3(n)
       ) as a
       where dt<cast(cast(@year+1 as char(4))+'-01-01' as date)
     )
    ,cte2
  as (select dt
            ,yyyymm
            ,wd
            ,row_number() over(partition by yyyymm,wd order by dt) as wdn
        from cte1
     )
select datepart(year,min(b.fulldate)) as [year]
      ,left(datename(month,min(b.fulldate)),3) as [month]
      ,min(b.fulldate) as rule_1_dates
      ,max(c.fulldate) as rule_2_dates
  from cte2 as a
       left outer join @workingdates as b
                    on b.fulldate>=a.dt
                   and b.wd<a.yyyymm*100+100
                   and a.wd=4
       left outer join @workingdates as c
                    on c.fulldate<=a.dt
                   and c.wd>a.yyyymm*100
                   and a.wd=0
 where (a.wd=4 and a.wdn=2)
    or (a.wd=0 and a.wdn=3)
 group by a.yyyymm
 order by rule_1_dates
;
3

hi

I tried to do this

hope it helps

:slight_smile:
:slight_smile:

I did this in steps
this below part not working
because I don't know how to define HOLIDAY ( what your holiday is )

part second Friday holiday

second Friday of each month (if second Friday is holiday for that month then that date would not be there working Dates.wd in this case i need previous working i.e Thursday or Wednesday whatever is working day available in table )

step 0drop create data 
create table workingDates (wd int , fulldate date , [dayname] varchar(30))
go 

insert into workingDates values (20190102,'2019-01-02','Wednesday')
insert into workingDates values (20190103,'2019-01-03','Thursday')
insert into workingDates values (20190104,'2019-01-04','Friday')
insert into workingDates values (20190107,'2019-01-07','Monday')
insert into workingDates values (20190108,'2019-01-08','Tuesday')
insert into workingDates values (20190109,'2019-01-09','Wednesday')
insert into workingDates values (20190110,'2019-01-10','Thursday')
insert into workingDates values (20190111,'2019-01-11','Friday')
insert into workingDates values (20190114,'2019-01-14','Monday')
insert into workingDates values (20190115,'2019-01-15','Tuesday')
insert into workingDates values (20190116,'2019-01-16','Wednesday')
insert into workingDates values (20190117,'2019-01-17','Thursday')
insert into workingDates values (20190118,'2019-01-18','Friday')
insert into workingDates values (20190121,'2019-01-21','Monday')
insert into workingDates values (20190122,'2019-01-22','Tuesday')
insert into workingDates values (20190123,'2019-01-23','Wednesday')
insert into workingDates values (20190124,'2019-01-24','Thursday')
insert into workingDates values (20190125,'2019-01-25','Friday')
insert into workingDates values (20190128,'2019-01-28','Monday')
insert into workingDates values (20190129,'2019-01-29','Tuesday')
insert into workingDates values (20190130,'2019-01-30','Wednesday')
insert into workingDates values (20190131,'2019-01-31','Thursday')
insert into workingDates values (20190201,'2019-02-01','Friday')
insert into workingDates values (20190204,'2019-02-04','Monday')
insert into workingDates values (20190205,'2019-02-05','Tuesday')
insert into workingDates values (20190206,'2019-02-06','Wednesday')
insert into workingDates values (20190207,'2019-02-07','Thursday')
insert into workingDates values (20190208,'2019-02-08','Friday')
insert into workingDates values (20190211,'2019-02-11','Monday')
insert into workingDates values (20190212,'2019-02-12','Tuesday')
insert into workingDates values (20190213,'2019-02-13','Wednesday')
insert into workingDates values (20190214,'2019-02-14','Thursday')
insert into workingDates values (20190215,'2019-02-15','Friday')
insert into workingDates values (20190218,'2019-02-18','Monday')
insert into workingDates values (20190219,'2019-02-19','Tuesday')
insert into workingDates values (20190220,'2019-02-20','Wednesday')
insert into workingDates values (20190221,'2019-02-21','Thursday')
insert into workingDates values (20190222,'2019-02-22','Friday')
insert into workingDates values (20190225,'2019-02-25','Monday')
insert into workingDates values (20190226,'2019-02-26','Tuesday')
insert into workingDates values (20190227,'2019-02-27','Wednesday')
insert into workingDates values (20190228,'2019-02-28','Thursday')
insert into workingDates values (20190301,'2019-03-01','Friday')
insert into workingDates values (20190304,'2019-03-04','Monday')
insert into workingDates values (20190305,'2019-03-05','Tuesday')
insert into workingDates values (20190306,'2019-03-06','Wednesday')
insert into workingDates values (20190307,'2019-03-07','Thursday')
insert into workingDates values (20190308,'2019-03-08','Friday')
insert into workingDates values (20190311,'2019-03-11','Monday')
insert into workingDates values (20190312,'2019-03-12','Tuesday')
insert into workingDates values (20190313,'2019-03-13','Wednesday')
insert into workingDates values (20190314,'2019-03-14','Thursday')
insert into workingDates values (20190315,'2019-03-15','Friday')
insert into workingDates values (20190318,'2019-03-18','Monday')
insert into workingDates values (20190319,'2019-03-19','Tuesday')
insert into workingDates values (20190320,'2019-03-20','Wednesday')
insert into workingDates values (20190321,'2019-03-21','Thursday')
insert into workingDates values (20190322,'2019-03-22','Friday')
insert into workingDates values (20190325,'2019-03-25','Monday')
insert into workingDates values (20190326,'2019-03-26','Tuesday')
insert into workingDates values (20190327,'2019-03-27','Wednesday')
insert into workingDates values (20190328,'2019-03-28','Thursday')
insert into workingDates values (20190329,'2019-03-29','Friday')
insert into workingDates values (20190401,'2019-04-01','Monday')
insert into workingDates values (20190402,'2019-04-02','Tuesday')
insert into workingDates values (20190403,'2019-04-03','Wednesday')
insert into workingDates values (20190404,'2019-04-04','Thursday')
insert into workingDates values (20190405,'2019-04-05','Friday')
insert into workingDates values (20190408,'2019-04-08','Monday')
insert into workingDates values (20190409,'2019-04-09','Tuesday')
insert into workingDates values (20190410,'2019-04-10','Wednesday')
insert into workingDates values (20190411,'2019-04-11','Thursday')
insert into workingDates values (20190412,'2019-04-12','Friday')
insert into workingDates values (20190415,'2019-04-15','Monday')
insert into workingDates values (20190416,'2019-04-16','Tuesday')
insert into workingDates values (20190417,'2019-04-17','Wednesday')
insert into workingDates values (20190418,'2019-04-18','Thursday')
insert into workingDates values (20190423,'2019-04-23','Tuesday')
insert into workingDates values (20190424,'2019-04-24','Wednesday')
insert into workingDates values (20190425,'2019-04-25','Thursday')
insert into workingDates values (20190426,'2019-04-26','Friday')
insert into workingDates values (20190429,'2019-04-29','Monday')
insert into workingDates values (20190430,'2019-04-30','Tuesday')
insert into workingDates values (20190501,'2019-05-01','Wednesday')
insert into workingDates values (20190502,'2019-05-02','Thursday')
insert into workingDates values (20190503,'2019-05-03','Friday')
insert into workingDates values (20190506,'2019-05-06','Monday')
insert into workingDates values (20190507,'2019-05-07','Tuesday')
insert into workingDates values (20190508,'2019-05-08','Wednesday')
insert into workingDates values (20190509,'2019-05-09','Thursday')
insert into workingDates values (20190510,'2019-05-10','Friday')
insert into workingDates values (20190513,'2019-05-13','Monday')
insert into workingDates values (20190514,'2019-05-14','Tuesday')
insert into workingDates values (20190515,'2019-05-15','Wednesday')
insert into workingDates values (20190516,'2019-05-16','Thursday')
insert into workingDates values (20190517,'2019-05-17','Friday')
insert into workingDates values (20190520,'2019-05-20','Monday')
insert into workingDates values (20190521,'2019-05-21','Tuesday')
insert into workingDates values (20190522,'2019-05-22','Wednesday')
insert into workingDates values (20190523,'2019-05-23','Thursday')
insert into workingDates values (20190524,'2019-05-24','Friday')
insert into workingDates values (20190527,'2019-05-27','Monday')
insert into workingDates values (20190528,'2019-05-28','Tuesday')
insert into workingDates values (20190529,'2019-05-29','Wednesday')
insert into workingDates values (20190530,'2019-05-30','Thursday')
insert into workingDates values (20190531,'2019-05-31','Friday')
insert into workingDates values (20190603,'2019-06-03','Monday')
insert into workingDates values (20190604,'2019-06-04','Tuesday')
insert into workingDates values (20190605,'2019-06-05','Wednesday')
insert into workingDates values (20190606,'2019-06-06','Thursday')
insert into workingDates values (20190607,'2019-06-07','Friday')
insert into workingDates values (20190610,'2019-06-10','Monday')
insert into workingDates values (20190611,'2019-06-11','Tuesday')
insert into workingDates values (20190612,'2019-06-12','Wednesday')
insert into workingDates values (20190613,'2019-06-13','Thursday')
insert into workingDates values (20190614,'2019-06-14','Friday')
insert into workingDates values (20190617,'2019-06-17','Monday')
insert into workingDates values (20190618,'2019-06-18','Tuesday')
insert into workingDates values (20190619,'2019-06-19','Wednesday')
insert into workingDates values (20190620,'2019-06-20','Thursday')
insert into workingDates values (20190621,'2019-06-21','Friday')
insert into workingDates values (20190624,'2019-06-24','Monday')
insert into workingDates values (20190625,'2019-06-25','Tuesday')
insert into workingDates values (20190626,'2019-06-26','Wednesday')
insert into workingDates values (20190627,'2019-06-27','Thursday')
insert into workingDates values (20190628,'2019-06-28','Friday')
insert into workingDates values (20190701,'2019-07-01','Monday')
insert into workingDates values (20190702,'2019-07-02','Tuesday')
insert into workingDates values (20190703,'2019-07-03','Wednesday')
insert into workingDates values (20190704,'2019-07-04','Thursday')
insert into workingDates values (20190705,'2019-07-05','Friday')
insert into workingDates values (20190708,'2019-07-08','Monday')
insert into workingDates values (20190709,'2019-07-09','Tuesday')
insert into workingDates values (20190710,'2019-07-10','Wednesday')
insert into workingDates values (20190711,'2019-07-11','Thursday')
insert into workingDates values (20190712,'2019-07-12','Friday')
insert into workingDates values (20190715,'2019-07-15','Monday')
insert into workingDates values (20190716,'2019-07-16','Tuesday')
insert into workingDates values (20190717,'2019-07-17','Wednesday')
insert into workingDates values (20190718,'2019-07-18','Thursday')
insert into workingDates values (20190719,'2019-07-19','Friday')
insert into workingDates values (20190722,'2019-07-22','Monday')
insert into workingDates values (20190723,'2019-07-23','Tuesday')
insert into workingDates values (20190724,'2019-07-24','Wednesday')
insert into workingDates values (20190725,'2019-07-25','Thursday')
insert into workingDates values (20190726,'2019-07-26','Friday')
insert into workingDates values (20190729,'2019-07-29','Monday')
insert into workingDates values (20190730,'2019-07-30','Tuesday')
insert into workingDates values (20190731,'2019-07-31','Wednesday')
insert into workingDates values (20190801,'2019-08-01','Thursday')
insert into workingDates values (20190802,'2019-08-02','Friday')
insert into workingDates values (20190805,'2019-08-05','Monday')
insert into workingDates values (20190806,'2019-08-06','Tuesday')
insert into workingDates values (20190807,'2019-08-07','Wednesday')
insert into workingDates values (20190808,'2019-08-08','Thursday')
insert into workingDates values (20190809,'2019-08-09','Friday')
insert into workingDates values (20190812,'2019-08-12','Monday')
insert into workingDates values (20190813,'2019-08-13','Tuesday')
insert into workingDates values (20190814,'2019-08-14','Wednesday')
insert into workingDates values (20190815,'2019-08-15','Thursday')
insert into workingDates values (20190816,'2019-08-16','Friday')
insert into workingDates values (20190819,'2019-08-19','Monday')
insert into workingDates values (20190820,'2019-08-20','Tuesday')
insert into workingDates values (20190821,'2019-08-21','Wednesday')
insert into workingDates values (20190822,'2019-08-22','Thursday')
insert into workingDates values (20190823,'2019-08-23','Friday')
insert into workingDates values (20190826,'2019-08-26','Monday')
insert into workingDates values (20190827,'2019-08-27','Tuesday')
insert into workingDates values (20190828,'2019-08-28','Wednesday')
insert into workingDates values (20190829,'2019-08-29','Thursday')
insert into workingDates values (20190830,'2019-08-30','Friday')
insert into workingDates values (20190902,'2019-09-02','Monday')
insert into workingDates values (20190903,'2019-09-03','Tuesday')
insert into workingDates values (20190904,'2019-09-04','Wednesday')
insert into workingDates values (20190905,'2019-09-05','Thursday')
insert into workingDates values (20190906,'2019-09-06','Friday')
insert into workingDates values (20190909,'2019-09-09','Monday')
insert into workingDates values (20190910,'2019-09-10','Tuesday')
insert into workingDates values (20190911,'2019-09-11','Wednesday')
insert into workingDates values (20190912,'2019-09-12','Thursday')
insert into workingDates values (20190913,'2019-09-13','Friday')
insert into workingDates values (20190916,'2019-09-16','Monday')
insert into workingDates values (20190917,'2019-09-17','Tuesday')
insert into workingDates values (20190918,'2019-09-18','Wednesday')
insert into workingDates values (20190919,'2019-09-19','Thursday')
insert into workingDates values (20190920,'2019-09-20','Friday')
insert into workingDates values (20190923,'2019-09-23','Monday')
insert into workingDates values (20190924,'2019-09-24','Tuesday')
insert into workingDates values (20190925,'2019-09-25','Wednesday')
insert into workingDates values (20190926,'2019-09-26','Thursday')
insert into workingDates values (20190927,'2019-09-27','Friday')
insert into workingDates values (20190930,'2019-09-30','Monday')
insert into workingDates values (20191001,'2019-10-01','Tuesday')
insert into workingDates values (20191002,'2019-10-02','Wednesday')
insert into workingDates values (20191003,'2019-10-03','Thursday')
insert into workingDates values (20191004,'2019-10-04','Friday')
insert into workingDates values (20191007,'2019-10-07','Monday')
insert into workingDates values (20191008,'2019-10-08','Tuesday')
insert into workingDates values (20191009,'2019-10-09','Wednesday')
insert into workingDates values (20191010,'2019-10-10','Thursday')
insert into workingDates values (20191011,'2019-10-11','Friday')
insert into workingDates values (20191014,'2019-10-14','Monday')
insert into workingDates values (20191015,'2019-10-15','Tuesday')
insert into workingDates values (20191016,'2019-10-16','Wednesday')
insert into workingDates values (20191017,'2019-10-17','Thursday')
insert into workingDates values (20191018,'2019-10-18','Friday')
insert into workingDates values (20191021,'2019-10-21','Monday')
insert into workingDates values (20191022,'2019-10-22','Tuesday')
insert into workingDates values (20191023,'2019-10-23','Wednesday')
insert into workingDates values (20191024,'2019-10-24','Thursday')
insert into workingDates values (20191025,'2019-10-25','Friday')
insert into workingDates values (20191028,'2019-10-28','Monday')
insert into workingDates values (20191029,'2019-10-29','Tuesday')
insert into workingDates values (20191030,'2019-10-30','Wednesday')
insert into workingDates values (20191031,'2019-10-31','Thursday')
insert into workingDates values (20191101,'2019-11-01','Friday')
insert into workingDates values (20191104,'2019-11-04','Monday')
insert into workingDates values (20191105,'2019-11-05','Tuesday')
insert into workingDates values (20191106,'2019-11-06','Wednesday')
insert into workingDates values (20191107,'2019-11-07','Thursday')
insert into workingDates values (20191108,'2019-11-08','Friday')
insert into workingDates values (20191111,'2019-11-11','Monday')
insert into workingDates values (20191112,'2019-11-12','Tuesday')
insert into workingDates values (20191113,'2019-11-13','Wednesday')
insert into workingDates values (20191114,'2019-11-14','Thursday')
insert into workingDates values (20191115,'2019-11-15','Friday')
insert into workingDates values (20191118,'2019-11-18','Monday')
insert into workingDates values (20191119,'2019-11-19','Tuesday')
insert into workingDates values (20191120,'2019-11-20','Wednesday')
insert into workingDates values (20191121,'2019-11-21','Thursday')
insert into workingDates values (20191122,'2019-11-22','Friday')
insert into workingDates values (20191125,'2019-11-25','Monday')
insert into workingDates values (20191126,'2019-11-26','Tuesday')
insert into workingDates values (20191127,'2019-11-27','Wednesday')
insert into workingDates values (20191128,'2019-11-28','Thursday')
insert into workingDates values (20191129,'2019-11-29','Friday')
insert into workingDates values (20191202,'2019-12-02','Monday')
insert into workingDates values (20191203,'2019-12-03','Tuesday')
insert into workingDates values (20191204,'2019-12-04','Wednesday')
insert into workingDates values (20191205,'2019-12-05','Thursday')
insert into workingDates values (20191206,'2019-12-06','Friday')
insert into workingDates values (20191209,'2019-12-09','Monday')
insert into workingDates values (20191210,'2019-12-10','Tuesday')
insert into workingDates values (20191211,'2019-12-11','Wednesday')
insert into workingDates values (20191216,'2019-12-16','Monday')
insert into workingDates values (20191217,'2019-12-17','Tuesday')
insert into workingDates values (20191218,'2019-12-18','Wednesday')
insert into workingDates values (20191219,'2019-12-19','Thursday')
insert into workingDates values (20191220,'2019-12-20','Friday')
insert into workingDates values (20191224,'2019-12-24','Tuesday')
insert into workingDates values (20191227,'2019-12-27','Friday')
insert into workingDates values (20191230,'2019-12-30','Monday')
insert into workingDates values (20191231,'2019-12-31','Tuesday')
go
step 1 create and populate holiday table 
DROP TABLE [dbo].[dimdate] 

go 

CREATE TABLE [dbo].[dimdate] 
  ( 
     [dimdateid]         [INT] NOT NULL PRIMARY KEY, 
     [datevalue]         [DATE] NOT NULL UNIQUE, 
     [day] AS Day(datevalue), 
     [week] AS Datepart(week, datevalue), 
     [month] AS Month(datevalue), 
     [quarter] AS Datepart(quarter, datevalue), 
     [year] AS Year(datevalue), 
     [dayofweek] AS Datepart(weekday, datevalue), 
     [iscanadianholiday] [BIT] NOT NULL DEFAULT ((0)), 
     [isusholiday]       [BIT] NOT NULL DEFAULT ((0)), 
  ) 

go 

DECLARE @StartDate DATE 
DECLARE @EndDate DATE 

SELECT @StartDate = Min(fulldate) 
FROM   workingdates 

SELECT @EndDate = Max(fulldate) 
FROM   workingdates; 

WITH n10(n) 
     AS (SELECT 1 
         FROM   (VALUES (0), 
                        (1), 
                        (2), 
                        (3), 
                        (4), 
                        (5), 
                        (6), 
                        (7), 
                        (8), 
                        (9)) v(n)), 
     n100(n) 
     AS (SELECT 1 
         FROM   n10, 
                n10 n), 
     n10000(n) 
     AS (SELECT 1 
         FROM   n100, 
                n100 n), 
     n100000(n) 
     AS (SELECT 1 
         FROM   n10, 
                n10000 n), 
     n 
     AS (SELECT TOP (Datediff(day, @startdate, @enddate) + 1) n = Row_number() 
           OVER( 
             ORDER BY (SELECT NULL)) - 1 
         FROM   n100000) 
INSERT INTO [dbo].[dimdate] 
            (dimdateid, 
             datevalue) 
SELECT Cast(CONVERT(CHAR(8), insertdate, 112) AS INT), 
       insertdate 
FROM   n 
       CROSS apply (SELECT Dateadd(day, n, @Startdate)) d(insertdate);
4
step 3 get nth weekday function 
ALTER FUNCTION dbo.Nthweekday(@first DATETIME,---'20110601 
                              @nth   TINYINT,-- Which of them - 1st, 2nd, etc. 
                              @dow   TINYINT -- Day of week we want 
) 
returns DATETIME 
AS 
  BEGIN 
      -- Note: Returns a date in a later month if @nth is too large 
      DECLARE @result DATETIME 

      SET @result = @first + 7 * ( @nth - 1 ) 

      RETURN @result + ( 7 + @dow - Datepart(weekday, @result) )%7 
  END 
--select  dbo.NthWeekDay('20110601',4,6)
step 4 ,2ndfriday … 3rd Friday .. put into temp table 
SELECT yr, 
       mn, 
       dbo.Nthweekday(Cast(Cast(Datepart(year, fulldate) AS VARCHAR) 
                           + '-' 
                           + Cast( Datepart(month, fulldate) AS VARCHAR) 
                           + '-' + '01' AS DATETIME), 2, 5) AS [2NDfriday], 
       dbo.Nthweekday(Cast(Cast(Datepart(year, fulldate) AS VARCHAR) 
                           + '-' 
                           + Cast( Datepart(month, fulldate) AS VARCHAR) 
                           + '-' + '01' AS DATETIME), 3, 5) AS [3RDfriday] 
INTO   abc 
FROM   (SELECT Datename(month, fulldate) AS MN, 
               Datepart(year, fulldate)  AS YR, 
               fulldate 
        FROM   workingdates) X
step 5 .. NextMondayAfter3rdFriday .. 2ndFridayHoliday into temp table 
SELECT yr, 
       mn, 
       [2ndfriday], 
       CASE 
         WHEN [2ndfriday] IS NOT NULL THEN NULL--previous workingDates DAY  
         ELSE [2ndfriday] 
       END            AS [2ndFridayHoliday], 
       [3rdfriday], 
       Dateadd(day, ( Datediff(day, ( ( 2 + 5 ) % 7 ), [3rdfriday]) / 7 ) * 7 + 
                    7, ( ( 
       2 + 5 ) % 7 )) AS [NextMondayAfter3rdFriday] 
INTO   def 
FROM   abc a 
       LEFT JOIN [dimdate] b 
              ON a.[2ndfriday] = b.[isusholiday]
step 6 final SQL 
SELECT a.yr, 
       a.mn, 
       a.[2ndfriday], 
       CASE 
         WHEN a.[2ndfridayholiday] IS NOT NULL THEN Max(b.fulldate) 
       END AS [2ndFridayHolidayPreviousWorkingDay], 
       a.[3rdfriday], 
       a.[nextmondayafter3rdfriday] 
FROM   def a 
       JOIN workingdates b 
         ON a.[2ndfriday] > b.fulldate 
GROUP  BY a.yr, 
          a.mn, 
          a.[2ndfriday], 
          a.[2ndfridayholiday], 
          a.[3rdfriday], 
          a.[nextmondayafter3rdfriday]
5

Final Output

image
image.png2328×788 23.1 KB