不做作业
问题描述
我有 3 列的主日期表workingDates
<块引用>一个.wd:(type int ) 此列包含 int 格式的所有工作日期，用于一年的周末和节假日不包括在内.
B.fulldate(数据类型日期):与 wd 相同，但采用日期格式.
c.dayname :这包含工作日名称，如星期一、星期二.
我想按照以下规则为每个月生成一个包含两个日期(两列)的列表.
一个.每个月的第二个星期五(如果第二个星期五是该月的假期，那么该日期不会出现在workingDates.wd 中，在这种情况下，我需要以前的工作，即:星期四或星期三，无论表中可用的工作日)
B.每个月的第三个星期五之后的星期一(在这种情况下，如果星期一是假期，我需要下一个工作日，即星期二或星期三，无论表中可用的工作日)
在这种情况下，我需要您的帮助来创建查询.
预期输出:
年月 rule_1_dates rule_2_dates2019 一月 20190111 20190121
等等…….
基表workingDates数据:
声明 @workingDates 表 (wd int , fulldate date , [dayname] varchar(30))插入@workingDates 值 (20190102,'2019-01-02','Wednesday')插入@workingDates 值 (20190103,'2019-01-03','Thursday')插入@workingDates 值 (20190104,'2019-01-04','Friday')插入@workingDates 值 (20190107,'2019-01-07','Monday')插入@workingDates 值 (20190108,'2019-01-08','Tuesday')插入@workingDates 值 (20190109,'2019-01-09','Wednesday')插入@workingDates 值 (20190110,'2019-01-10','Thursday')插入@workingDates 值 (20190111,'2019-01-11','Friday')插入@workingDates 值 (20190114,'2019-01-14','Monday')插入@workingDates 值(20190115、'2019-01-15'、'Tuesday')插入@workingDates 值 (20190116,'2019-01-16','Wednesday')插入@workingDates 值 (20190117,'2019-01-17','Thursday')插入@workingDates 值 (20190118,'2019-01-18','Friday')插入@workingDates 值 (20190121,'2019-01-21','Monday')插入@workingDates 值 (20190122,'2019-01-22','Tuesday')插入@workingDates 值 (20190123,'2019-01-23','Wednesday')插入@workingDates 值 (20190124,'2019-01-24','Thursday')插入@workingDates 值 (20190125,'2019-01-25','Friday')插入@workingDates 值 (20190128,'2019-01-28','Monday')插入@workingDates 值 (20190129,'2019-01-29','Tuesday')插入@workingDates 值 (20190130,'2019-01-30','Wednesday')插入@workingDates 值 (20190131,'2019-01-31','Thursday')插入@workingDates 值 (20190201,'2019-02-01','Friday')插入@workingDates 值 (20190204,'2019-02-04','Monday')插入@workingDates 值 (20190205,'2019-02-05','Tuesday')插入@workingDates 值(20190206,'2019-02-06','Wednesday')插入@workingDates 值 (20190207,'2019-02-07','Thursday')插入@workingDates 值 (20190208,'2019-02-08','Friday')插入@workingDates 值 (20190211,'2019-02-11','Monday')插入@workingDates 值 (20190212,'2019-02-12','Tuesday')插入@workingDates 值(20190213,'2019-02-13','Wednesday')插入@workingDates 值 (20190214,'2019-02-14','Thursday')插入@workingDates 值 (20190215,'2019-02-15','Friday')插入@workingDates 值 (20190218,'2019-02-18','Monday')插入@workingDates 值 (20190219,'2019-02-19','Tuesday')插入@workingDates 值 (20190220,'2019-02-20','Wednesday')插入@workingDates 值 (20190221,'2019-02-21','Thursday')插入@workingDates 值 (20190222,'2019-02-22','Friday')插入@workingDates 值 (20190225,'2019-02-25','Monday')插入@workingDates 值 (20190226,'2019-02-26','Tuesday')插入@workingDates 值 (20190227,'2019-02-27','Wednesday')插入@workingDates 值 (20190228,'2019-02-28','Thursday')插入@workingDates 值 (20190301,'2019-03-01','Friday')插入@workingDates 值 (20190304,'2019-03-04','Monday')插入@workingDates 值 (20190305,'2019-03-05','Tuesday')插入@workingDates 值 (20190306,'2019-03-06','Wednesday')插入@workingDates 值 (20190307,'2019-03-07','Thursday')插入@workingDates 值 (20190308,'2019-03-08','Friday')插入@workingDates 值 (20190311,'2019-03-11','Monday')插入@workingDates 值 (20190312,'2019-03-12','Tuesday')插入@workingDates 值(20190313,'2019-03-13','Wednesday')插入@workingDates 值 (20190314,'2019-03-14','Thursday')插入@workingDates 值 (20190315,'2019-03-15','Friday')插入@workingDates 值 (20190318,'2019-03-18','Monday')插入@workingDates 值 (20190319,'2019-03-19','Tuesday')插入@workingDates 值 (20190320,'2019-03-20','Wednesday')插入@workingDates 值 (20190321,'2019-03-21','Thursday')插入@workingDates 值 (20190322,'2019-03-22','Friday')插入@workingDates 值 (20190325,'2019-03-25','Monday')插入@workingDates 值 (20190326,'2019-03-26','Tuesday')插入@workingDates 值 (20190327,'2019-03-27','Wednesday')插入@workingDates 值 (20190328,'2019-03-28','Thursday')插入@workingDates 值 (20190329,'2019-03-29','Friday')插入@workingDates 值 (20190401,'2019-04-01','Monday')插入@workingDates 值 (20190402,'2019-04-02','Tuesday')插入@workingDates 值(20190403,'2019-04-03','Wednesday')插入@workingDates 值 (20190404,'2019-04-04','Thursday')插入@workingDates 值 (20190405,'2019-04-05','Friday')插入@workingDates 值 (20190408,'2019-04-08','Monday')插入@workingDates 值 (20190409,'2019-04-09','Tuesday')插入@workingDates 值 (20190410,'2019-04-10','Wednesday')插入@workingDates 值 (20190411,'2019-04-11','Thursday')插入@workingDates 值 (20190412,'2019-04-12','Friday')插入@workingDates 值 (20190415,'2019-04-15','Monday')插入@workingDates 值 (20190416,'2019-04-16','Tuesday')插入@workingDates 值 (20190417,'2019-04-17','Wednesday')插入@workingDates 值 (20190418,'2019-04-18','Thursday')插入@workingDates 值 (20190423,'2019-04-23','Tuesday')插入@workingDates 值 (20190424,'2019-04-24','Wednesday')插入@workingDates 值 (20190425,'2019-04-25','Thursday')插入@workingDates 值 (20190426,'2019-04-26','Friday')插入@workingDates 值 (20190429,'2019-04-29','Monday')插入@workingDates 值 (20190430,'2019-04-30','Tuesday')插入@workingDates 值 (20190501,'2019-05-01','Wednesday')插入@workingDates 值 (20190502,'2019-05-02','Thursday')插入@workingDates 值 (20190503,'2019-05-03','Friday')插入@workingDates 值 (20190506,'2019-05-06','Monday')插入@workingDates 值 (20190507,'2019-05-07','Tuesday')插入@workingDates 值 (20190508,'2019-05-08','Wednesday')插入@workingDates 值 (20190509,'2019-05-09','Thursday')插入@workingDates 值 (20190510,'2019-05-10','Friday')插入@workingDates 值 (20190513,'2019-05-13','Monday')插入@workingDates 值 (20190514,'2019-05-14','Tuesday')插入@workingDates 值 (20190515,'2019-05-15','Wednesday')插入@workingDates 值 (20190516,'2019-05-16','Thursday')插入@workingDates 值 (20190517,'2019-05-17','Friday')插入@workingDates 值 (20190520,'2019-05-20','Monday')插入@workingDates 值 (20190521,'2019-05-21','Tuesday')插入@workingDates 值 (20190522,'2019-05-22','Wednesday')插入@workingDates 值 (20190523,'2019-05-23','Thursday')插入@workingDates 值 (20190524,'2019-05-24','Friday')插入@workingDates 值 (20190527,'2019-05-27','Monday')插入@workingDates 值 (20190528,'2019-05-28','Tuesday')插入@workingDates 值 (20190529,'2019-05-29','Wednesday')插入@workingDates 值 (20190530,'2019-05-30','Thursday')插入@workingDates 值 (20190531,'2019-05-31','Friday')插入@workingDates 值 (20190603,'2019-06-03','Monday')插入@workingDates 值 (20190604,'2019-06-04','Tuesday')插入@workingDates 值 (20190605,'2019-06-05','Wednesday')插入@workingDates 值 (20190606,'2019-06-06','Thursday')插入@workingDates 值 (20190607,'2019-06-07','Friday')插入@workingDates 值 (20190610,'2019-06-10','Monday')插入@workingDates 值(20190611、'2019-06-11'、'Tuesday')插入@workingDates 值 (20190612,'2019-06-12','Wednesday')插入@workingDates 值 (20190613,'2019-06-13','Thursday')插入@workingDates 值 (20190614,'2019-06-14','Friday')插入@workingDates 值 (20190617,'2019-06-17','Monday')插入@workingDates 值 (20190618,'2019-06-18','Tuesday')插入@workingDates 值 (20190619,'2019-06-19','Wednesday')插入@workingDates 值 (20190620,'2019-06-20','Thursday')插入@workingDates 值 (20190621,'2019-06-21','Friday')插入@workingDates 值 (20190624,'2019-06-24','Monday')插入@workingDates 值 (20190625,'2019-06-25','Tuesday')插入@workingDates 值 (20190626,'2019-06-26','Wednesday')插入@workingDates 值 (20190627,'2019-06-27','Thursday')插入@workingDates 值 (20190628,'2019-06-28','Friday')插入@workingDates 值 (20190701,'2019-07-01','Monday')插入@workingDates 值 (20190702,'2019-07-02','Tuesday')插入@workingDates 值(20190703,'2019-07-03','Wednesday')插入@workingDates 值 (20190704,'2019-07-04','Thursday')插入@workingDates 值(20190705,'2019-07-05','Friday')插入@workingDates 值 (20190708,'2019-07-08','Monday')插入@workingDates 值 (20190709,'2019-07-09','Tuesday')插入@workingDates 值(20190710,'2019-07-10','Wednesday')插入@workingDates 值 (20190711,'2019-07-11','Thursday')插入@workingDates 值 (20190712,'2019-07-12','Friday')插入@workingDates 值 (20190715,'2019-07-15','Monday')插入@workingDates 值 (20190716,'2019-07-16','Tuesday')插入@workingDates 值 (20190717,'2019-07-17','Wednesday')插入@workingDates 值 (20190718,'2019-07-18','Thursday')插入@workingDates 值 (20190719,'2019-07-19','Friday')插入@workingDates 值 (20190722,'2019-07-22','Monday')插入@workingDates 值 (20190723,'2019-07-23','Tuesday')插入@workingDates 值 (20190724,'2019-07-24','Wednesday')插入@workingDates 值 (20190725,'2019-07-25','Thursday')插入@workingDates 值 (20190726,'2019-07-26','Friday')插入@workingDates 值 (20190729,'2019-07-29','Monday')插入@workingDates 值 (20190730,'2019-07-30','Tuesday')插入@workingDates 值 (20190731,'2019-07-31','Wednesday')插入@workingDates 值(20190801,'2019-08-01','Thursday')插入@workingDates 值 (20190802,'2019-08-02','Friday')插入@workingDates 值 (20190805,'2019-08-05','Monday')插入@workingDates 值(20190806,'2019-08-06','Tuesday')插入@workingDates 值 (20190807,'2019-08-07','Wednesday')插入@workingDates 值 (20190808,'2019-08-08','Thursday')插入@workingDates 值 (20190809,'2019-08-09','Friday')插入@workingDates 值 (20190812,'2019-08-12','Monday')插入@workingDates 值 (20190813,'2019-08-13','Tuesday')插入@workingDates 值 (20190814,'2019-08-14','Wednesday')插入@workingDates 值 (20190815,'2019-08-15','Thursday')插入@workingDates 值 (20190816,'2019-08-16','Friday')插入@workingDates 值 (20190819,'2019-08-19','Monday')插入@workingDates 值 (20190820,'2019-08-20','Tuesday')插入@workingDates 值 (20190821,'2019-08-21','Wednesday')插入@workingDates 值 (20190822,'2019-08-22','Thursday')插入@workingDates 值 (20190823,'2019-08-23','Friday')插入@workingDates 值 (20190826,'2019-08-26','Monday')插入@workingDates 值 (20190827,'2019-08-27','Tuesday')插入@workingDates 值 (20190828,'2019-08-28','Wednesday')插入@workingDates 值 (20190829,'2019-08-29','Thursday')插入@workingDates 值 (20190830,'2019-08-30','Friday')插入@workingDates 值 (20190902,'2019-09-02','Monday')插入@workingDates 值 (20190903,'2019-09-03','Tuesday')插入@workingDates 值 (20190904,'2019-09-04','Wednesday')插入@workingDates 值 (20190905,'2019-09-05','Thursday')插入@workingDates 值(20190906,'2019-09-06','Friday')插入@workingDates 值 (20190909,'2019-09-09','Monday')插入@workingDates 值 (20190910,'2019-09-10','Tuesday')插入@workingDates 值(20190911、'2019-09-11'、'Wednesday')插入@workingDates 值 (20190912,'2019-09-12','Thursday')插入@workingDates 值 (20190913,'2019-09-13','Friday')插入@workingDates 值 (20190916,'2019-09-16','Monday')插入@workingDates 值(20190917、'2019-09-17'、'Tuesday')插入@workingDates 值 (20190918,'2019-09-18','Wednesday')插入@workingDates 值 (20190919,'2019-09-19','Thursday')插入@workingDates 值 (20190920,'2019-09-20','Friday')插入@workingDates 值 (20190923,'2019-09-23','Monday')插入@workingDates 值 (20190924,'2019-09-24','Tuesday')插入@workingDates 值 (20190925,'2019-09-25','Wednesday')插入@workingDates 值 (20190926,'2019-09-26','Thursday')插入@workingDates 值 (20190927,'2019-09-27','Friday')插入@workingDates 值 (20190930,'2019-09-30','Monday')插入@workingDates 值 (20191001,'2019-10-01','Tuesday')插入@workingDates 值 (20191002,'2019-10-02','Wednesday')插入@workingDates 值 (20191003,'2019-10-03','Thursday')插入@workingDates 值 (20191004,'2019-10-04','Friday')插入@workingDates 值 (20191007,'2019-10-07','Monday')插入@workingDates 值 (20191008,'2019-10-08','Tuesday')插入@workingDates 值 (20191009,'2019-10-09','Wednesday')插入@workingDates 值(20191010,'2019-10-10','Thursday')插入@workingDates 值 (20191011,'2019-10-11','Friday')插入@workingDates 值 (20191014,'2019-10-14','Monday')插入@workingDates 值 (20191015,'2019-10-15','Tuesday')插入@workingDates 值 (20191016,'2019-10-16','Wednesday')插入@workingDates 值(20191017,'2019-10-17','Thursday')插入@workingDates 值 (20191018,'2019-10-18','Friday')插入@workingDates 值 (20191021,'2019-10-21','Monday')插入@workingDates 值 (20191022,'2019-10-22','Tuesday')插入@workingDates 值 (20191023,'2019-10-23','Wednesday')插入@workingDates 值 (20191024,'2019-10-24','Thursday')插入@workingDates 值 (20191025,'2019-10-25','Friday')插入@workingDates 值 (20191028,'2019-10-28','Monday')插入@workingDates 值 (20191029,'2019-10-29','Tuesday')插入@workingDates 值 (20191030,'2019-10-30','Wednesday')插入@workingDates 值 (20191031,'2019-10-31','Thursday')插入@workingDates 值 (20191101,'2019-11-01','Friday')插入@workingDates 值 (20191104,'2019-11-04','Monday')插入@workingDates 值 (20191105,'2019-11-05','Tuesday')插入@workingDates 值 (20191106,'2019-11-06','Wednesday')插入@workingDates 值 (20191107,'2019-11-07','Thursday')插入@workingDates 值 (20191108,'2019-11-08','Friday')插入@workingDates 值 (20191111,'2019-11-11','Monday')插入@workingDates 值 (20191112,'2019-11-12','Tuesday')插入@workingDates 值 (20191113,'2019-11-13','Wednesday')插入@workingDates 值 (20191114,'2019-11-14','Thursday')插入@workingDates 值 (20191115,'2019-11-15','Friday')插入@workingDates 值 (20191118,'2019-11-18','Monday')插入@workingDates 值(20191119,'2019-11-19','Tuesday')插入@workingDates 值 (20191120,'2019-11-20','Wednesday')插入@workingDates 值 (20191121,'2019-11-21','Thursday')插入@workingDates 值 (20191122,'2019-11-22','Friday')插入@workingDates 值 (20191125,'2019-11-25','Monday')插入@workingDates 值 (20191126,'2019-11-26','Tuesday')插入@workingDates 值 (20191127,'2019-11-27','Wednesday')插入@workingDates 值 (20191128,'2019-11-28','Thursday')插入@workingDates 值 (20191129,'2019-11-29','Friday')插入@workingDates 值 (20191202,'2019-12-02','Monday')插入@workingDates 值(20191203,'2019-12-03','Tuesday')插入@workingDates 值 (20191204,'2019-12-04','Wednesday')插入@workingDates 值 (20191205,'2019-12-05','Thursday')插入@workingDates 值 (20191206,'2019-12-06','Friday')插入@workingDates 值 (20191209,'2019-12-09','Monday')插入@workingDates 值 (20191210,'2019-12-10','Tuesday')插入@workingDates 值(20191211,'2019-12-11','Wednesday')插入@workingDates 值 (20191216,'2019-12-16','Monday')插入@workingDates 值(20191217、'2019-12-17'、'Tuesday')插入@workingDates 值(20191218,'2019-12-18','Wednesday')插入@workingDates 值 (20191219,'2019-12-19','Thursday')插入@workingDates 值 (20191220,'2019-12-20','Friday')插入@workingDates 值 (20191224,'2019-12-24','Tuesday')插入@workingDates 值 (20191227,'2019-12-27','Friday')插入@workingDates 值 (20191230,'2019-12-30','Monday')插入@workingDates 值 (20191231,'2019-12-31','Tuesday')
解决方案
完整代码如下
1) 我用 突尼斯 => 表 @tabholiday
填写了假期日期随意填写您的国家假期日期
2) 我生成了一个包含给定年份(此处为 2019 年)所有日期的动态表 => 表 @calendar
3) 天数(如果星期五是假期，则为之前每个月的第二个星期五)存储在表@tab_friday
4) 天数(如果星期一是假期的话，第三个星期五之后的星期一)存储在表@tab_monday
5) 那么最终的结果就是这两个表的联合.
将 SQL RECURSIVITY 与 CTE 和 Union All 结合使用
将@tabholiday 声明为表(holiday_date 日期)将@tab_friday 声明为表(m int，date_found 日期)将@tab_monday 声明为表(m int，date_found 日期)插入@tabholiday values('2019/01/07'),('2019/01/11'),('2019/03/20'),('2019/04/09'),('2019/05'/01'),('2019/07/25'),('2019/08/13'),('2019/10/15')将@calendar 声明为表(wd int,fulldate date,dayname varchar(20),m int,weekday_rank_month int,holiday int)--Kemal AL GAZZAH 的脚本--19/04/2019设置日期格式 dmy--填写@Calendar表------------;WITH cte as (SELECT cast('01/01/2019' as date) as dUNION ALL SELECT dateadd(day,1,d) AS d FROM cte WHERE year(dateadd(day,1,d))=2019 )插入@calendar SELECT cast(replace(d,'-','') as int) wd,d fulldate,案例日期部分(w，d)当 1 然后是星期日"当 2 然后是星期一"当 3 然后是星期二"当 4 然后是星期三"当 5 然后是星期四"当 6 然后是星期五"当 7 然后是星期六"结尾[日名],月(d) [M],row_number() over (partition by datepart(w,d),month(d) order by d) weekday_rank_month,case isnull(t2.holiday_date,'') when '' then 0 else 1 end [holiday]FROM cte t1 左外连接 @tabholiday t2 on t1.d=t2.holiday_date按 d 排序选项(MAXRECURSION 366)；--1)-填写周五表与 cte as(从@calendar t1 中选择 t1.m [Month],t1.fulldate[secondfriday],t1.holiday,t2.fulldate[secondfriday1]外部应用(从@calendar t2 中选择 top 1 * 其中 t2.fulldate t1.fulldate 按 fulldate 排序)t2外部应用(从@calendar t3 中选择 top 1 * where fulldate > t2.fulldate and Holiday=0 and month(fulldate)=t1.m order by fulldate )t3/* 左外连接 @calendar t3 在 t2.holiday=1 和 t3.holiday=0 和 dateadd(day,1,t1.fulldate)=t3.fulldate在 t2.holiday=1 和 t3.holiday=1 和 dateadd(day,2,t1.fulldate)=t4.fulldate*/上左外连接 @calendar t4其中 t1.weekday_rank_month=3 和 t1.dayname='friday')插入@tab_monday选择[月]，案件当假期 = 0 时 d1否则 d2end [第三个星期五之后的星期一(或星期一是假期之后)] from cte--select * from @tab_monday--3)-周五和周一表之间的联接select t1.m [Month],t1.date_found [Second Friday or before],t2.date_found[Monday after third friday or after] from @tab_friday t1 inner join @tab_monday t2 on t1.m=t2.m I have master dates table workingDates that have 3 columns
a. wd:(type int ) This column has all working dates in int format for a year weekends and holidays are not included.
b. fulldate (datatype date): same as wd but in date format.
c. dayname : this contains weekdayname like monday,tuesday.
I want to generate a list of two dates(two columns ) for each month as per the following rule.
a. second Friday of each month (if second Friday is a holiday for that month then that date would not be there in workingDates.wd, in this case, I need previous working i.e: Thursday or Wednesday whatever working day available in the table )
b. Monday after third Friday for each month ( in this case if Monday is a holiday I need next working day i.e Tuesday or Wednesday whatever is working day available in the 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’)
解决方案
Here is the complete code
1) I filled the holiday dates with those of Tunisia => Table @tabholiday
Feel free to fill it with your country holiday dates
2) I generated a dynmaic table with all dates of a given year (here 2019) => Table @calendar
3) The days (second Friday for each month of before if friday is a holiday) are stored in table @tab_friday
4) The days (Monday after third Friday of after if Monday is a holiday) are stored in table @tab_monday
5) Then the Final result is the jointure of these two tables.
using SQL RECURSIVITY with CTE and Union All
declare @tabholiday as table(holiday_date date)
declare @tab_friday as table (m int,date_found date)
declare @tab_monday as table (m int,date_found date)
insert into @tabholiday values('2019/01/07'),('2019/01/11'),('2019/03/20'),('2019/04/09'),('2019/05/01'),('2019/07/25'),('2019/08/13'),('2019/10/15')
declare @calendar as table(wd int,fulldate date,dayname varchar(20),m int,weekday_rank_month int,holiday int)
--Script by Kemal AL GAZZAH
--19/04/2019
set dateformat dmy
--Filling the @Calendar Table------------
;WITH cte as (SELECT cast('01/01/2019' as date) as d
UNION ALL SELECT dateadd(day,1,d) AS d FROM cte WHERE year(dateadd(day,1,d))=2019 )
insert into @calendar SELECT cast(replace(d,'-','') as int) wd,d fulldate,
case datepart(w,d)
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Satuday'
end
[dayname] ,month(d) [M]
,row_number() over (partition by datepart(w,d),month(d) order by d) weekday_rank_month,case isnull(t2.holiday_date,'') when '' then 0 else 1 end [holiday]
FROM cte t1 left outer join @tabholiday t2 on t1.d=t2.holiday_date
order by d
OPTION (MAXRECURSION 366);
--1)-Filling Friday Table
with cte as(
select t1.m [Month],t1.fulldate[secondfriday],t1.holiday,t2.fulldate[secondfriday1] from @calendar t1
outer apply(select top 1 * from @calendar t2 where t2.fulldate < t1.fulldate and t2.holiday=0 order by fulldate desc) t2
/*left outer join @calendar t2 on t1.weekday_rank_month=2 and t1.dayname='friday' and t1.holiday=1 and dateadd(day,-1,t1.fulldate)=t2.fulldate and t2.holiday=0
left outer join @calendar t3 on t1.weekday_rank_month=2 and t1.dayname='friday' and t1.holiday=1 and dateadd(day,-2,t1.fulldate)=t3.fulldate and t2.holiday=1*/
where t1.weekday_rank_month=2 and t1.dayname='friday')
insert into @tab_friday select month,case
when holiday =0 then secondfriday
else secondfriday1
end [second friday or before (if that friday is a holiday)] from cte;
--2)-Filling Monday Table
;
with cte as
(
select t1.m[Month],t2.fulldate d1,t2.holiday,t3.fulldate d2 from @calendar t1
outer apply(select top 1 * from @calendar t2 where t2.dayname='monday' and t2.fulldate > t1.fulldate order by fulldate) t2
outer apply (select top 1 * from @calendar t3 where fulldate > t2.fulldate and holiday=0 and month(fulldate)=t1.m order by fulldate ) t3
/*left outer join @calendar t3 on t2.holiday=1 and t3.holiday=0 and dateadd(day,1,t1.fulldate)=t3.fulldate
left outer join @calendar t4 on t2.holiday=1 and t3.holiday=1 and dateadd(day,2,t1.fulldate)=t4.fulldate*/
where t1.weekday_rank_month=3 and t1.dayname='friday')
insert into @tab_monday
select
[Month],
case
when holiday=0 then d1
else d2
end [Monday after third friday(or after f monday is a holiday)] from cte
--select * from @tab_monday
--3)-Jointure Betwwen Friday and Monday Table
select t1.m [Month],t1.date_found [Second Friday or before],t2.date_found[Monday after third friday or after] from @tab_friday t1 inner join @tab_monday t2 on t1.m=t2.m
