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
- It will generate a list of dates between the given start date and end date.
- 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
- 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.
- If the weekday only occurs four times, the Fifth column returns null.
- You need to provide the Year and Month range.
- You need to provide the week day name to extract e.g. Monday, Tuesday etc.
- 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
