Get the Iteration of a Weekday in a Month on a Virtual Calendar

There are many situations where you need to identify the iteration of a weekday within a given month. Most commonly though is for the identification of public holidays e.g., the 4th Thursday in November – Thanksgiving in USA.

There are no built-in methods to get this information in any SQL-based platform I have used thus far – if you know of one, please let me know.

So, I created a table-valued function to extract this information from a given Year and Month. However, in order to do this, we need a base calendar table to work with. So, as a bonus, I have included a table-valued function to generate a virtual calendar table we can use for this purpose.

Final output you can expect when you call the function for 2022-January for Mondays:

Virtual Calendar Table

CREATE OR ALTER FUNCTION [dbo].[utvf_Generate_VirtualDateTable]
(
    @pStartDate AS DATE
    , @pEndDate AS DATE
) 
RETURNS TABLE
AS
/*=====================================================================================================================================================
Author:			Dian Germishuizen
Description:	Generate a list of numbers between the pStartDate and the pEndDate
Original Inspiration: https://www.itprotoday.com/sql-server/packing-intervals-priorities
-------------------------------------------------------------------------------------------------------------------------------------------------------
Changes Made:	
Date			Author					Description (What changes were made to this code on this day)
----------		------------------		---------------------------------------------------------------------------------------------------------------
2022-06-16		Dian Germishuizen		Created
-------------------------------------------------------------------------------------------------------------------------------------------------------
TODO: 
--Place code to test object here
SELECT *
FROM [dbo].[utvf_Generate_VirtualDateTable]('2022-01-01', '2022-01-10')
=====================================================================================================================================================*/
RETURN
WITH [Level1]
AS 
(   /* Generate a 2 row table that contains a 1 for each value */
    SELECT [Number] FROM (SELECT 1 AS [Number] UNION ALL SELECT 1 AS [Number]) AS [Table]
)
, [Level2] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT 1 AS [Number] FROM [Level1] AS [One] CROSS JOIN [Level1] AS [Two]
)
, [Level3] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT 1 AS [Number] FROM [Level2] AS [One] CROSS JOIN [Level2] AS [Two]
)
, [Level4] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT 1 AS [Number] FROM [Level3] AS [One] CROSS JOIN [Level3] AS [Two]
)
, [Level5] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT 1 AS [Number] FROM [Level4] AS [One] CROSS JOIN [Level4] AS [Two]
)
, [Level6] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT 1 AS [Number] FROM [Level5] AS [One] CROSS JOIN [Level5] AS [Two]
)
, [Numbers] 
AS 
(   /* Cross join the previous level to itself to get the exponensial number of rows */
    SELECT ROW_NUMBER() OVER(ORDER BY [Number]) AS [RowNumber] FROM [Level6]
)
, [BaseListOfDates]
AS
(   /* 
        For the list of numbers generated, create a row number and form that a list of dates 
        starting at the @pStartDate value and ending at the @pEndDate value
    */
    SELECT TOP(DATEDIFF(DAY, @pStartDate, @pEndDate) + 1)
        [RowNumber]
        , DATEADD(DAY, ( [RowNumber] - 1 ), @pStartDate) AS [Date]
    FROM [Numbers] 
)
/* Apply additional calculations on the base date value */
SELECT 
    [RowNumber]
    , [Date]
    , CONVERT(VARCHAR, [Date], 112) AS [DateKey]
    , DATEPART(YEAR, [Date]) AS [Year] /*2019*/
    , DATEPART(QUARTER, [Date]) AS [Quarter] /*1*/
    , DATEPART(MONTH, [Date]) AS [Month] /*2*/
    , DATENAME(MONTH, [Date]) AS [MonthName] /*February*/
    , LEFT(DATENAME(MONTH, [Date]), 3) AS [MonthNameShort] /* Feb */
    , DATEPART(WEEK, [Date]) AS [Week] /*7*/
    , DATEPART(DAY, [Date]) AS [Day] /*14*/
    , DATENAME(WEEKDAY, [Date]) AS [DayName] /*Thursday*/
    , LEFT(DATENAME(WEEKDAY, [Date]), 3) AS [DayNameShort] /* Thu */
    , DATEPART(dw, [Date])  AS [DayNumberOfWeek] /* 4 */
FROM [BaseListOfDates]
GO

Key aspects of the virtual date table

  1. It will generate a list of dates between the given start date and end date.
  2. It uses a succession of cross joins to generate the list of rows and then calculates the dates from that. The original idea for this row number generation came from itprotoday – https://www.itprotoday.com/sql-server/packing-intervals-priorities
    • It starts by creating a 2-row table from hardcoding a SELECT that unions with another SELECT
    • From there it CROSS JOINs the table to itself to increase the number of rows by an exponent of 2.
    • This pattern repeats 6 times to generate a large list of rows.

Get All Iterations Of Weekday In Year Month Code

CREATE OR ALTER FUNCTION [dbo].[ufn_Get_AllIterationsOfWeekdayInYearMonth]
(
    @pYear INT /*The year to investigate*/
    , @pMonth INT /*The month to investigate*/
    , @pWeekdayName VARCHAR(255) /*The week day to return e.g. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday*/
)
RETURNS TABLE
AS
/*=====================================================================================================================================================
Author:			Dian Germishuizen
Description:	Procedures to get the 1st, 2nd, 3rd, 4th or 5th iteration of a given week day in a given month of a given year
Steps
1. For the given Year, For the given Month, generate a table with the dates and the days of the week
2. Filter the days to the required week day e.g. Monday, Tuesday etc. 
3. Add a row number of the records in ascending order
4. Return the record where the row number is the same as the Iteration parameter
If the given paramters dont result in a date, NULL is returned
-------------------------------------------------------------------------------------------------------------------------------------------------------
Changes Made:	
Date			Author					Description (What changes were made to this code on this day)
----------		------------------		---------------------------------------------------------------------------------------------------------------
2022-06-16		Dian Germishuizen		Created
-------------------------------------------------------------------------------------------------------------------------------------------------------
--Test the procedure
SELECT * FROM [dbo].[ufn_Get_AllIterationsOfWeekdayInYearMonth] (2022, 1, 'Monday')
=====================================================================================================================================================*/
RETURN
    WITH [IterationsOfWeekdayCTE]
    AS
    (   /* 
            Generate a list of dates for the year and month provided, only the dates that fall on the weekday provided
            Add a row number to indicate the order they fall in
        */
        SELECT 
            1 AS [PivotPoint]
            , [Date]
            , [DayName]
            , ROW_NUMBER() OVER(
                ORDER BY [Date] ASC
            ) AS [RowNumber_Ascending]
        FROM [dbo].[utvf_Generate_VirtualDateTable] (DATEFROMPARTS(@pYear, @pMonth, 1), EOMONTH(DATEFROMPARTS(@pYear, @pMonth, 1)))
        WHERE [Year] = @pYear
            AND [Month] = @pMonth
            AND [DayName] = @pWeekdayName
    )
    SELECT
        MAX([PivotTable].[1]) AS [First]
        , MAX([PivotTable].[2]) AS [Second]
        , MAX([PivotTable].[3]) AS [Third]
        , MAX([PivotTable].[4]) AS [Fourth]
        , MAX([PivotTable].[5]) AS [Fifth]
        /* TO get the latest iteration, we need to do a max on all the dates that fall on the indicated weekday */
        , (
            SELECT MAX([Value])
            FROM 
            (
                SELECT MAX([PivotTable].[1]) AS [Value]
                UNION ALL 
                SELECT MAX([PivotTable].[2]) AS [Value]
                UNION ALL 
                SELECT MAX([PivotTable].[3]) AS [Value]
                UNION ALL 
                SELECT MAX([PivotTable].[4]) AS [Value]
                UNION ALL 
                SELECT MAX([PivotTable].[5]) AS [Value]
            ) AS [LastValueTable]
        ) AS [Last]
    FROM [IterationsOfWeekdayCTE]
    /* We need to pivot the date to get each date in a specific column */
    PIVOT
    (
        MAX([Date])
        FOR [RowNumber_Ascending] IN ([1], [2], [3], [4], [5])
    ) AS [PivotTable]
    GROUP BY [PivotPoint]
    

Key Aspects Of The Function

  1. The output table provides all instances of the weekday in the month e.g. the First, Second, Third, Fourth, Fifth and Last iteration as a single row table.
  2. If the weekday only occurs four times, the Fifth column returns null.
  3. You need to provide the Year and Month range.
  4. You need to provide the week day name to extract e.g. Monday, Tuesday etc.
  5. This function relies on the virtual date table above. However, if you have a calendar table already, it can be adapted to read from that instead.

If you like what I do please consider supporting me on Ko-Fi