June 28, 2022 at 10:48 pm
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
June 29, 2022 at 5:40 am
This was removed by the editor as SPAM
June 29, 2022 at 12:26 pm
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
June 29, 2022 at 5:03 pm
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
Change is inevitable... Change for the better is not.
June 29, 2022 at 10:24 pm
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
June 30, 2022 at 3:47 am
This was removed by the editor as SPAM
June 30, 2022 at 6:15 pm
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.
June 30, 2022 at 6:25 pm
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.
June 30, 2022 at 7:12 pm
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 30, 2022 at 9:00 pm
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
Change is inevitable... Change for the better is not.
June 30, 2022 at 9:17 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply