Get Future Occurrences of Black Friday in SQL Server

-- Declare # of years you want dates for, the month, the day, and the occurrence of that day within the month
declare @years tinyint = 15;
declare @month_name varchar(10) = 'November';
declare @day_name varchar(10) = 'Friday';
declare @week_num tinyint = 4;

-- Build the calendar
WITH calendar
AS
(
  SELECT CAST(getdate() as date) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM calendar
  WHERE DATEADD(dd, 1, [date]) <= dateadd(year,@years,getdate())
)

-- Get the month and days that apply
, month_days as (
SELECT [date]
  ,row_number() over (partition by year([date]),month([date]) order by date asc) as [week_num]
FROM calendar
where 1=1
and DATENAME(weekday,[date]) = @day_name
and datename(month,[date]) = @month_name
)

-- Get the dates where the occurrence matches
select [date]
from month_days
where week_num = @week_num
OPTION (MAXRECURSION 0) --Unlimited recursion; limit the number of years in parameter at the top
;