Adding First Monday, etc of Month to Calendar Table

Transact-SQL
1

I have a calendar table for most of my date calculations, but I am finding I now need to be able to locate dates based on being the 1st, 2nd, 3rd, 4th day name of the month.

I am thinking I need to add a column to the calendar table (say calOccurrenceOfMonth) which would hold 1,2,3,4,5 depending on which occurrence the date is.

So...
2016-07-04 would be 1 for 1st Monday of the Month
...
2016-07-11 would be 2 for 2nd Monday of the Month
2016-07-12 would be 2 for 2nd Tuesday of the Month
and so on...

The when I need to get the 2nd Tuesday of July I can...

Select calDate from Calendar Where calMonth = 7 and calOccurrenceOfMonth = 2 and CalDay = 2

Can someone help as to how to write the query to insert this column to my Calendar table ?

Thanks

Phil

2

One way to do this, is to use APPLY:

DECLARE @tbl_Calendar AS TABLE
( calDate DATE,
  calYear INT,
  calMonth TINYINT,
  calOccurrenceOfMonth TINYINT
)

;WITH cteSource
AS ( SELECT CAST('2016-07-04' AS DATE) AS calDate UNION ALL
     SELECT CAST('2016-07-05' AS DATE)              UNION ALL
     SELECT CAST('2016-07-11' AS DATE)              UNION ALL
     SELECT CAST('2016-07-12' AS DATE)
)
,Calendar
AS ( SELECT calDate,YEAR(calDate) AS calYear,MONTH(calDate) AS calMonth , 0 AS calOccurrenceOfMonth
    FROM cteSource
)  



INSERT INTO @tbl_Calendar(calDate,calYear,calMonth,calOccurrenceOfMonth)
SELECT calDate,calYear,calMonth,calOccurrenceOfMonth
FROM Calendar


UPDATE C
SET C.calOccurrenceOfMonth = CA.calOccurrenceOfMonth
FROM
    @tbl_Calendar AS C
    CROSS APPLY
    (SELECT 
        COUNT(*)
    FROM 
        @tbl_Calendar AS C2
    WHERE
        C.calYear = C2.calYear
        AND C.calMonth = C2.calMonth
        AND C2.calDate <= C.calDate
        AND DATEPART(DW,C.calDate) = DATEPART(DW,C2.calDate)
    )CA(calOccurrenceOfMonth)
    

SELECT * FROM @tbl_Calendar

Output for this:

calDate    calYear     calMonth calOccurrenceOfMonth
2016-07-04 2016        7        1
2016-07-05 2016        7        1
2016-07-11 2016        7        2
2016-07-12 2016        7        2
3

Excellent - many thanks

Phil

4

My pleasure!