Generate list of working day according to rule defined.

  • pnpsql

    SSC-Addicted

    Points: 456

    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')

  • ScottPletcher

    SSC Guru

    Points: 97940

    ;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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply