Find Iteration of Day of Week in Month
It's actually surprisingly easy using DAY(), as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

select case when DAY(yourdate) &lt;=7  then 1
            when DAY(yourdate) &lt;=14 then 2
            when DAY(yourdate) &lt;=21 then 3
            when DAY(yourdate) &lt;=28 then 4
            else 5
        end as occurance
from yourtable; 


(case short-circuits)