April 19, 2019 at 12:20 pm
I have table workingDates that has 3 columns
wd:(type int ) This column has all working dates in int format for a year weekends and holidays are not included.
fulldate (datatype date): same as wd but in date format.
dayname : this contains weekdayname like monday,tuesday.
I want to generate list of two dates for each month as per following rule.
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 )
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')
April 19, 2019 at 8:20 pm
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cteTally10 c1 CROSS JOIN cteTally10 c2
)
SELECT YEAR(day_14) AS year, DATENAME(MONTH, day_14) AS month, rule_1_date, rule_2_date
FROM cteTally100 months
CROSS APPLY (
SELECT
DATEADD(DAY, 13, DATEADD(MONTH, months.number - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0,
GETDATE()), 0))) AS day_14
) AS date_calcs_1
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 4, day_14) % 7, day_14) AS second_friday
) AS date_calcs_2
CROSS APPLY (
SELECT DATEADD(DAY, 7, second_friday) AS third_friday
) AS date_calcs_3
CROSS APPLY (
SELECT MAX(wd) AS rule_1_date
FROM #workingdates
WHERE wd <= CONVERT(varchar(8), second_friday, 112)
) AS rule_1_date
CROSS APPLY (
SELECT MIN(wd) AS rule_2_date
FROM #workingdates
WHERE wd > CONVERT(varchar(8), third_friday, 112)
) AS rule_2_date
WHERE months.number BETWEEN 1 AND 12
ORDER BY day_14
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply