如何根据定义的规则生成不包括假期的工作日列表 [英] How to generate list of weekday dates excluding holidays as per defined rule
本文介绍了如何根据定义的规则生成不包括假期的工作日列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有 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
这篇关于如何根据定义的规则生成不包括假期的工作日列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
