asked on
declare @yr int, @mo tinyint
set @yr = 2014
set @mo = 6
;
with dates( dt )
as
(
-- select 1st day of month constructed from year and month inputs
select convert( datetime,
convert( char(8),
@yr * 10000 + @mo * 100 + 1
)
)
union all -- facilitates recursion
-- add in remainder of days in month
select dt + 1
from dates
-- keeps adding until the next day would be 1st of next month
where day( dt + 1 ) <> 1
)
, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
select dt, datepart( weekday, dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt desc )
from dates
)
select dt, dw, occurrence, occurrence_reverse
from dates_tagged
where dw = 3 and occurrence = 2 --I added this
;