Post reply

Generate list of working day according to rule defined.

  • April 19, 2019 at 12:20 pm

    #3466147

    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

    #3471029 

    ;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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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