Technical Article

WorkDay Functions

,

I recently needed to create a job that would populate a cache on the 4th business day of the month.

I found a few solutions that were pretty good.  Here is my approach to this.

-- First, we need a table of holidays:
CREATE TABLE [dbo].[Holidays]
(
[Date] [date] NOT NULL,
[HolidayText] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
GRANT SELECT ON dbo.Holidays TO PUBLIC
INSERT dbo.Holidays
VALUES
 ('2019-01-01', 'New Year''s Day')
,('2019-01-21', 'Martin Luther King Day')
,('2019-02-18', 'President''s Day')
,('2019-05-27', 'Memorial Day')
,('2019-07-04', 'Independence Day')
,('2019-09-02', 'Labour Day')
,('2019-11-11', 'Veterans'' Day')
,('2019-11-28', 'Thanksgiving Day')
,('2019-11-29', 'Day after Thanksgiving')
,('2019-12-25', 'Christmas Day')
,('2019-12-24', 'Christmas Eve Day')
,('2020-01-01', 'New Year''s Day')
,('2020-01-20', 'Martin Luther King Day')
,('2020-02-17', 'President''s Day')
,('2020-05-25', 'Memorial Day')
,('2020-07-04', 'Independence Day')
,('2020-09-07', 'Labour Day')
,('2020-11-11', 'Veterans'' Day')
,('2020-11-27', 'Day after Thanksgiving')
,('2020-11-26', 'Thanksgiving Day')
,('2020-12-25', 'Christmas Day')
,('2021-01-01', 'New Year''s Day')
,('2021-01-18', 'Martin Luther King Day')
,('2021-02-15', 'President''s Day')
,('2021-05-31', 'Memorial Day')
,('2021-07-04', 'Independence Day')
,('2021-09-06', 'Labour Day')
,('2021-11-11', 'Veterans'' Day')
,('2021-11-26', 'Day after Thanksgiving')
,('2021-11-25', 'Thanksgiving Day')
,('2021-12-25', 'Christmas Day')

 

And here are the functions

IF OBJECT_ID(N'dbo.WorkDayOfMonth') IS NOT NULL
    DROP FUNCTION dbo.WorkDayOfMonth;
GO
CREATE FUNCTION dbo.WorkDayOfMonth
(    @Date date
)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @dt2 DATE = DATEFROMPARTS(YEAR(@Date)- 1, 12, 31),
            @workday INT;
    SELECT @workday = T.workday
    FROM
    (
        SELECT D.date1,
               ROW_NUMBER() OVER (PARTITION BY MONTH(date1), YEAR(date1) ORDER BY DAY(D.Date1)) AS workday
        FROM
        (
            SELECT DATEADD(DAY, M.Num, @dt2) AS date1
            FROM
            (
                SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS Num
                FROM sys.all_objects
            ) AS M
            WHERE DATEADD(DAY, Num, @dt2) <= DATEFROMPARTS(YEAR(@Date), 12, 31)
        ) AS D
        LEFT JOIN dbo.Holidays H
             ON D.date1 = H.[Date]
        WHERE H.[Date] IS NULL
              AND DATEPART(WEEKDAY, D.date1)
              BETWEEN 2 AND 6  -- sunday = 1
    ) AS T
    WHERE T.date1 = @Date;
    RETURN (@workday);
END;
GO

IF OBJECT_ID(N'dbo.WorkDayOfYear') IS NOT NULL
    DROP FUNCTION dbo.WorkDayOfYear;
GO

CREATE FUNCTION dbo.WorkDayOfYear
(    @Date date
)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @dt2 DATE = DATEFROMPARTS(YEAR(@Date) - 1, 12, 31),
            @workday INT;
    SELECT @workday = T.workday
    FROM
    (
        SELECT D.date1,
               ROW_NUMBER() OVER (PARTITION BY YEAR(date1) ORDER BY date1) AS workday
        FROM
        (
            SELECT DATEADD(DAY, M.Num, @dt2) AS date1
            FROM
            (
                SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS Num
                FROM sys.all_objects
            ) AS M
            WHERE DATEADD(DAY, Num, @dt2) <= DATEFROMPARTS(YEAR(@Date), 12, 31)
        ) AS D
        LEFT JOIN dbo.Holidays H
             ON D.date1 = H.[Date]
        WHERE H.[Date] IS NULL
              AND DATEPART(WEEKDAY, D.date1)
              BETWEEN 2 AND 6  -- sunday = 1
    ) AS T
    WHERE T.date1 = @Date;
    RETURN (@workday);
END;
GO

IF OBJECT_ID(N'dbo.AddWorkDays') IS NOT NULL
    DROP FUNCTION dbo.AddWorkDays;
GO
CREATE FUNCTION dbo.AddWorkDays
(
    @Date DATE
,@NumDays int
)
RETURNS DATE
WITH EXECUTE AS CALLER
AS
BEGIN
IF @NumDays = 0 
RETURN @Date
-- there are approx 21 working days in a month.  @numdays * 2 should be enough to cover it
    DECLARE @dt2 DATE = DATEADD(DAY,  @numdays*2, @Date),
            @result date;
       SELECT @result = T.date1
   FROM ( SELECT D.date1,
               ROW_NUMBER() OVER ( ORDER BY D.Num) AS workday
        FROM
        (
            SELECT DATEADD(DAY,  M.Num*SIGN(@NumDays), @Date) AS date1, M.Num
            FROM
            (
                SELECT ROW_NUMBER() OVER (ORDER BY s1.object_id) AS Num
                FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
            ) AS M
            WHERE Num < ABS(@NumDays)*2
        ) AS D
        LEFT JOIN dbo.Holidays H
             ON D.date1 = H.[Date]
        WHERE H.[Date] IS NULL
              AND DATEPART(WEEKDAY, D.date1)
              BETWEEN 2 AND 6  -- sunday = 1
  ) AS T WHERE T.workday =ABS(@NumDays)
RETURN @result

END;
GO

IF OBJECT_ID(N'dbo.ListWorkDays') IS NOT NULL
    DROP FUNCTION dbo.ListWorkDays;
GO
CREATE FUNCTION dbo.ListWorkDays 
(
@FromDate DATE, 
@ToDate DATE
)
RETURNS 
@ListWorkDays TABLE 
(
Date1 DATE, 
WorkDay int
)
AS
BEGIN
INSERT @ListWorkDays
(
    Date1,
    WorkDay
)
 SELECT D.date1,
        ROW_NUMBER() OVER (ORDER BY D.Num) AS workday
        FROM
        (
            SELECT DATEADD(DAY,  M.Num , @FromDate) AS date1, M.Num
            FROM
            (
                SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS Num
                FROM sys.all_objects
            ) AS M
            WHERE Num <= DATEDIFF(DAY, @FromDate, @ToDate)
        ) AS D
        LEFT JOIN dbo.Holidays H
             ON D.date1 = H.[Date]
        WHERE H.[Date] IS NULL
              AND DATEPART(WEEKDAY, D.date1)
              BETWEEN 2 AND 6  -- sunday = 1
RETURN 
END
GO
GRANT EXEC ON dbo.WorkDayOfMonth TO PUBLIC
GRANT EXEC ON dbo.WorkDayOfYear TO PUBLIC
GRANT EXEC ON dbo.AddWorkDays TO PUBLIC
GRANT SELECT ON dbo.ListWorkDays  TO PUBLIC
go 

 

usage examples:

select dbo.WorkDayOfMonth(getdate());

if(dbo.WorkDayOfMonth(@thatdate) >0)

print 'is workday'

select dbo.WorkDayOfYear(getdate());

select dbo.AddWorkDays('2019-01-01', 22);  --accepts negative numbers too

SELECT * FROM dbo.ListWorkDays('2019-01-01', '2019-09-05')

In my SQL Job

If (dbo.WorkDayOfMonth(GetDate())= 4

Begin

EXEC dbo.populateCache(@asofDate)

end

Notes:

For my needs, a tally table of about 1000 rows was adequate.   sys.all_objects worked just fine for that.

but say you had someone who worked one day a week and you wanted to find when they would hit their 1000th workday, you'll need a larger tally table

sys.all_objects s1 CROSS JOIN sys.all_objects s2   can easily yield 1M rows. (depends on how many objects you have in your database)

You can easily replace Holidays with say an employee's calendar.  - or their AntiCalendar - their list of holidays.

If appropriate, simply union it with Holidays.

 

 

 

 

  

Rate

Share

Share

Rate