Adding First Monday, etc of Month to Calendar Table

Transact-SQL
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