Replace calendar table multiplier column with date logic

  • Hello All,

    It's been a while. See the DDL/DML. I have a multiplier field associated with each day in a calendar table that I would like to replace with date logic. Updating the multiplier data is a yearly chore requiring an UI/data entry but really we would like to encapsulate the logic rather than relying on an end user entering data correctly. I am looking at replacing the multiplier field with date logic perhaps encapsulating in an a function.

    The rules look consistent but I can't wrap my ahead around the code required. I have entered a comment in the table to explaining the logic for each date.

    I believe these are the rules:

    If all the previous days in the current month are not business days then add 1 to the count of the previous non business days; else

    Count the subsequent number of non business days and add 1 for the current day; The count can not extend into the subsequent month

    Thanks if you can help.

    DROP TABLE IF EXISTS #Calendar

    CREATE TABLE #Calendar( CalDate DATE,DayName NVARCHAR(30),BusinessDay CHAR(1),Multiplier INT,Comment VARCHAR(200) )

    INSERT INTO #Calendar (CalDate,DayName,BusinessDay,Multiplier,Comment)

    VALUES

    ( N'2021-12-20T00:00:00',N'Monday','Y',1 ,'1 because the next day is a business day'),

    ( N'2021-12-21T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2021-12-22T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2021-12-23T00:00:00',N'Thursday','Y',4 ,'4 because the next 3 days are not business days'),

    ( N'2021-12-24T00:00:00',N'Friday','N',1 ,'If BusinessDay = N then 1'),

    ( N'2021-12-25T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2021-12-26T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2021-12-27T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2021-12-28T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2021-12-29T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2021-12-30T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2021-12-31T00:00:00',N'Friday','Y',1 ,'The next days are not business days but they are in the next month'),

    ( N'2022-01-01T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-01-02T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-01-03T00:00:00',N'Monday','Y',3 ,'All the previous days in the month are not business days'),

    ( N'2022-01-04T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-01-05T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-01-06T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-01-07T00:00:00',N'Friday','Y',3 ,'Typical mid-month friday logic. There are previous business days in the month.3 because the next 2 days are business days'),

    ( N'2022-01-08T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-01-09T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-01-10T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-01-11T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-01-12T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-01-13T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-01-14T00:00:00',N'Friday','Y',4 ,'Typical mid-month friday logic with a subsequent Monday holiday'),

    ( N'2022-01-15T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-01-16T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-01-17T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-01-18T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-01-19T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-01-20T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-01-21T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-01-22T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-01-23T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-01-24T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-01-25T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-01-26T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-01-27T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-01-28T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-01-29T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-01-30T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-01-31T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-02-01T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-02-02T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-02-03T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-02-04T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-02-05T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-02-06T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-02-07T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-02-08T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-02-09T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-02-10T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-02-11T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-02-12T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-02-13T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-02-14T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-02-15T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-02-16T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-02-17T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-02-18T00:00:00',N'Friday','Y',4 ,'Typical mid-month friday logic with a subsequent Monday holiday'),

    ( N'2022-02-19T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-02-20T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-02-21T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-02-22T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-02-23T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-02-24T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-02-25T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-02-26T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-02-27T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-02-28T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-03-01T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-03-02T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-03-03T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-03-04T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-03-05T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-03-06T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-03-07T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-03-08T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-03-09T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-03-10T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-03-11T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-03-12T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-03-13T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-03-14T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-03-15T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-03-16T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-03-17T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-03-18T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-03-19T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-03-20T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-03-21T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-03-22T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-03-23T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-03-24T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-03-25T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-03-26T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-03-27T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-03-28T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-03-29T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-03-30T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-03-31T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-04-01T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-04-02T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-04-03T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-04-04T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-04-05T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-04-06T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-04-07T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-04-08T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-04-09T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-04-10T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-04-11T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-04-12T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-04-13T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-04-14T00:00:00',N'Thursday','Y',4 ,'4 because the next 3 days are not business days'),

    ( N'2022-04-15T00:00:00',N'Friday','N',1 ,NULL),

    ( N'2022-04-16T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-04-17T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-04-18T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-04-19T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-04-20T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-04-21T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-04-22T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-04-23T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-04-24T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-04-25T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-04-26T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-04-27T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-04-28T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-04-29T00:00:00',N'Friday','Y',2 ,'Month end is on a non-business day. 2 since the Sunday gets applied to the following Monday'),

    ( N'2022-04-30T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-05-01T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-05-02T00:00:00',N'Monday','Y',2 ,'2. All the the previous days of the month are non business days.'),

    ( N'2022-05-03T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-05-04T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-05-05T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-05-06T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-05-07T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-05-08T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-05-09T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-05-10T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-05-11T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-05-12T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-05-13T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-05-14T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-05-15T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-05-16T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-05-17T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-05-18T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-05-19T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-05-20T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-05-21T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-05-22T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-05-23T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-05-24T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-05-25T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-05-26T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-05-27T00:00:00',N'Friday','Y',4 ,NULL),

    ( N'2022-05-28T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-05-29T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-05-30T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-05-31T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-06-01T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-06-02T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-06-03T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-06-04T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-06-05T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-06-06T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-06-07T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-06-08T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-06-09T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-06-10T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-06-11T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-06-12T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-06-13T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-06-14T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-06-15T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-06-16T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-06-17T00:00:00',N'Friday','Y',4 ,NULL),

    ( N'2022-06-18T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-06-19T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-06-20T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-06-21T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-06-22T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-06-23T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-06-24T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-06-25T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-06-26T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-06-27T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-06-28T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-06-29T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-06-30T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-07-01T00:00:00',N'Friday','Y',4 ,NULL),

    ( N'2022-07-02T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-07-03T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-07-04T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-07-05T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-07-06T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-07-07T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-07-08T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-07-09T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-07-10T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-07-11T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-07-12T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-07-13T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-07-14T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-07-15T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-07-16T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-07-17T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-07-18T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-07-19T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-07-20T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-07-21T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-07-22T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-07-23T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-07-24T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-07-25T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-07-26T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-07-27T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-07-28T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-07-29T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-07-30T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-07-31T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-08-01T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-08-02T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-08-03T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-08-04T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-08-05T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-08-06T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-08-07T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-08-08T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-08-09T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-08-10T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-08-11T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-08-12T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-08-13T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-08-14T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-08-15T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-08-16T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-08-17T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-08-18T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-08-19T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-08-20T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-08-21T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-08-22T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-08-23T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-08-24T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-08-25T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-08-26T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-08-27T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-08-28T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-08-29T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-08-30T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-08-31T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-09-01T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-09-02T00:00:00',N'Friday','Y',4 ,NULL),

    ( N'2022-09-03T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-09-04T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-09-05T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-09-06T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-09-07T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-09-08T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-09-09T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-09-10T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-09-11T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-09-12T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-09-13T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-09-14T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-09-15T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-09-16T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-09-17T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-09-18T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-09-19T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-09-20T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-09-21T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-09-22T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-09-23T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-09-24T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-09-25T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-09-26T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-09-27T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-09-28T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-09-29T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-09-30T00:00:00',N'Friday','Y',1 ,NULL),

    ( N'2022-10-01T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-10-02T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-10-03T00:00:00',N'Monday','Y',3 ,NULL),

    ( N'2022-10-04T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-10-05T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-10-06T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-10-07T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-10-08T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-10-09T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-10-10T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-10-11T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-10-12T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-10-13T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-10-14T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-10-15T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-10-16T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-10-17T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-10-18T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-10-19T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-10-20T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-10-21T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-10-22T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-10-23T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-10-24T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-10-25T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-10-26T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-10-27T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-10-28T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-10-29T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-10-30T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-10-31T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-11-01T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-11-02T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-11-03T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-11-04T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-11-05T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-11-06T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-11-07T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-11-08T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-11-09T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-11-10T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-11-11T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-11-12T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-11-13T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-11-14T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-11-15T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-11-16T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-11-17T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-11-18T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-11-19T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-11-20T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-11-21T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-11-22T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-11-23T00:00:00',N'Wednesday','Y',2 ,'Thursday holiday'),

    ( N'2022-11-24T00:00:00',N'Thursday','N',1 ,NULL),

    ( N'2022-11-25T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-11-26T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-11-27T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-11-28T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-11-29T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-11-30T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-12-01T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-12-02T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-12-03T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-12-04T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-12-05T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-12-06T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-12-07T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-12-08T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-12-09T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-12-10T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-12-11T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-12-12T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-12-13T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-12-14T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-12-15T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-12-16T00:00:00',N'Friday','Y',3 ,NULL),

    ( N'2022-12-17T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-12-18T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-12-19T00:00:00',N'Monday','Y',1 ,NULL),

    ( N'2022-12-20T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-12-21T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-12-22T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-12-23T00:00:00',N'Friday','Y',4 ,NULL),

    ( N'2022-12-24T00:00:00',N'Saturday','N',1 ,NULL),

    ( N'2022-12-25T00:00:00',N'Sunday','N',1 ,NULL),

    ( N'2022-12-26T00:00:00',N'Monday','N',1 ,NULL),

    ( N'2022-12-27T00:00:00',N'Tuesday','Y',1 ,NULL),

    ( N'2022-12-28T00:00:00',N'Wednesday','Y',1 ,NULL),

    ( N'2022-12-29T00:00:00',N'Thursday','Y',1 ,NULL),

    ( N'2022-12-30T00:00:00',N'Friday','Y',2 ,'Count the Saturday but no the Sunday since its in the next month. '),

    ( N'2022-12-31T00:00:00',N'Saturday','N',1,NULL)

    SELECT

    CalDate,

    DayName,

    BusinessDay,

    Multiplier,

    Comment

    FROM #Calendar

    • This topic was modified 1 month, 2 weeks ago by  Chrissy321. Reason: Typo
  • This was removed by the editor as SPAM

  • This is a classic "gaps and islands" type situation imo. Not sure what's going on with SSC tho. There's no text editor and the font is really tiny now. Maybe it's time to see the eye Dr again

    In this case the gaps are created by comparing the 'BusinessDay' value to its LAG value ORDER BY CalDate. SUM OVER the gaps and PARTITION By month ORDER BY CalDate creates the row groupings 'grp' column. The 'island_day_count' values are created by summarizing GROUP BY month, BusinessDay, and 'grp'.

    This query creates the gaps and islands and then LEAD and LAG of the BusinessDay and island_day_count columns. Afaik based on this then CASE WHEN ... you could derive the multiplier values

    with

    gaps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap) as (

    select *, case when BusinessDay<>lag(BusinessDay) over (order by CalDate) then 1 else 0 end gap

    from #Calendar),

    grps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap, grp) as (

    select *, sum(gap) over (partition by eomonth(CalDate) order by CalDate) grp

    from gaps_cte),

    islands_cte(mon, BusinessDay, grp, start_dt, end_dt, island_day_count) as (

    select eomonth(CalDate), BusinessDay, grp,

    min(CalDate), max(CalDate), count(*)

    from grps_cte

    group by eomonth(CalDate), BusinessDay, grp)

    select *,

    lead(BusinessDay, 1, 0) over (partition by mon order by start_dt) lead_biz_day,

    lead(island_day_count, 1, 0) over (partition by mon order by start_dt) lead_island_day_count,

    lag(BusinessDay, 1, 0) over (partition by mon order by start_dt) lag_biz_day,

    lag(island_day_count, 1, 0) over (partition by mon order by start_dt) lag_island_day_count

    from islands_cte

    order by mon, start_dt;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I'm really curious about this request.  What is the intended use of the computed "Multiplier" column?

    If the answer is to determine which day is (for example) "3 business days away",  there are better/simpler ways to do this and additional years added to the calendar are actually a breeze to do auto-magically.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This query returns no rows

    with 
    gaps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap) as (
    select *, case when BusinessDay<>lag(BusinessDay) over (order by CalDate) then 1 else 0 end gap
    from #Calendar),
    grps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap, grp) as (
    select *, sum(gap) over (partition by eomonth(CalDate) order by CalDate) grp
    from gaps_cte),
    islands_cte(mon, BusinessDay, grp, start_dt, end_dt, island_day_count) as (
    select eomonth(CalDate), BusinessDay, grp,
    min(CalDate), max(CalDate), count(*)
    from grps_cte
    group by eomonth(CalDate), BusinessDay, grp),
    lead_lag_cte as (
    select *,
    lead(island_day_count, 1, 0) over (partition by mon order by start_dt) lead_island_day_count,
    lag(island_day_count, 1, 0) over (partition by mon order by start_dt) lag_island_day_count
    from islands_cte)
    select c.CalDate, c.[DayName], c.BusinessDay,
    1+isnull(llp.island_day_count, 0)+isnull(lls.island_day_count, 0) Multiplier
    FROM #Calendar c
    /* If all the previous days in the current month are not business days then add 1 to the count of the previous non business days*/
    left join lead_lag_cte llp on c.CalDate=dateadd(day, 1, llp.end_dt)
    and llp.lag_island_day_count=0
    and llp.BusinessDay='N'
    /* Count the subsequent number of non business days and add 1 for the current day */
    left join lead_lag_cte lls on c.CalDate=dateadd(day, -1, lls.start_dt)
    and lls.lag_island_day_count<>0
    and lls.BusinessDay='N'
    except
    SELECT CalDate, DayName, BusinessDay, Multiplier
    FROM #Calendar;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

  • Jeff Moden wrote:

    I'm really curious about this request.  What is the intended use of the computed "Multiplier" column?

    If the answer is to determine which day is (for example) "3 business days away",  there are better/simpler ways to do this and additional years added to the calendar are actually a breeze to do auto-magically.

    The purpose is a prorating reporting function. Say you get a data point only on business days and the requirement is to get an average of the data point for a particular month.

  • Steve Collins wrote:

    This query returns no rows

    with 
    gaps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap) as (
    select *, case when BusinessDay<>lag(BusinessDay) over (order by CalDate) then 1 else 0 end gap
    from #Calendar),
    grps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap, grp) as (
    select *, sum(gap) over (partition by eomonth(CalDate) order by CalDate) grp
    from gaps_cte),
    islands_cte(mon, BusinessDay, grp, start_dt, end_dt, island_day_count) as (
    select eomonth(CalDate), BusinessDay, grp,
    min(CalDate), max(CalDate), count(*)
    from grps_cte
    group by eomonth(CalDate), BusinessDay, grp),
    lead_lag_cte as (
    select *,
    lead(island_day_count, 1, 0) over (partition by mon order by start_dt) lead_island_day_count,
    lag(island_day_count, 1, 0) over (partition by mon order by start_dt) lag_island_day_count
    from islands_cte)
    select c.CalDate, c.[DayName], c.BusinessDay,
    1+isnull(llp.island_day_count, 0)+isnull(lls.island_day_count, 0) Multiplier
    FROM #Calendar c
    /* If all the previous days in the current month are not business days then add 1 to the count of the previous non business days*/
    left join lead_lag_cte llp on c.CalDate=dateadd(day, 1, llp.end_dt)
    and llp.lag_island_day_count=0
    and llp.BusinessDay='N'
    /* Count the subsequent number of non business days and add 1 for the current day */
    left join lead_lag_cte lls on c.CalDate=dateadd(day, -1, lls.start_dt)
    and lls.lag_island_day_count<>0
    and lls.BusinessDay='N'
    except
    SELECT CalDate, DayName, BusinessDay, Multiplier
    FROM #Calendar;

    Thanks for the additional code. I will need to find some time to study it a bit more. Using your original post I was able to devise a solution (although its does not seem as elegant as this code). My solution also matched up against the limited sample data I provided. When I ran my solutions against my actual historical data a few discrepancies appeared which turned out to be deviations from the accepted logic, data entry errors or logical errors. Thanks for posting.

  • This code produces the exact same results as Steve's but runs in about half the time.

    WITH BusinessDates AS
    (
    SELECT *, COALESCE(MAX(bd.BusinessDate) OVER(PARTITION BY MONTH(c.CalDate) ORDER BY c.CalDate ROWS UNBOUNDED PRECEDING), MIN(bd.BusinessDate) OVER(PARTITION BY MONTH(c.CalDate) ORDER BY c.CalDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS BusinessDateGrp
    FROM #Calendar AS c
    CROSS APPLY (VALUES(CASE WHEN c.BusinessDay = 'Y' THEN c.CalDate END)) bd(BusinessDate)
    )
    SELECT bd.CalDate
    , bd.DayName
    , bd.BusinessDay
    , CASE WHEN bd.BusinessDay = 'N' THEN 1 ELSE COUNT(*) OVER(PARTITION BY bd.BusinessDateGrp) END AS Multiplier
    FROM BusinessDates AS bd

    I believe that you should be able to replace "UNBOUNDED" with "3" without changing the results.  This assumes that you will never have more than three non-business days in a row.  If that is not the case, you can increase the value until it equals the maximum number of sequential non-business days.

    Drew

    • This reply was modified 1 month, 1 week ago by  drew.allen.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Just a tweak to Drew's awesome rendition...

    Drew uses the MONTH() function.  I believe (I've not tested it) that will limit his good code to being used against only 1 year of data at a time.  In the following code (which is mostly a reformatted, nearly identical version of his code), search for "Month#" to find the changes I made so that it can handle a mostly indeterminate number of years to get the number of months that have occurred since the Epoch Date of "0", which is the "Date Serial Number" for "01 Jan 1900", which is the Epoch Date for SQL Server and most Microsoft things.

       WITH BusinessDates AS
    (
    SELECT *
    ,BusinessDateGrp =
    COALESCE(
    MAX(bd.BusinessDate) OVER(PARTITION BY mm.Month# ORDER BY c.CalDate ROWS UNBOUNDED PRECEDING)
    ,MIN(bd.BusinessDate) OVER(PARTITION BY mm.Month# ORDER BY c.CalDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))
    FROM #Calendar AS c
    CROSS APPLY (VALUES(CASE WHEN c.BusinessDay = 'Y' THEN c.CalDate END)) bd(BusinessDate)
    CROSS APPLY (VALUES(DATEDIFF(mm,0,c.CalDate)))mm(Month#)
    )
    SELECT bd.CalDate
    ,bd.DayName
    ,bd.BusinessDay
    ,Multiplier = IIF(bd.BusinessDay = 'N',1,COUNT(*) OVER(PARTITION BY bd.BusinessDateGrp))
    FROM BusinessDates AS bd
    ORDER BY Caldate
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for the improvement Jeff.  I was thinking about that after I posted and realized that it would limit it to just a year.  The mod that I was going to suggest was replacing MONTH() with EOMONTH().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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