SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @now date = '2020-2-3'; --以今天為基準
declare @TheDateBefore400Years date = dateadd(year,-400,@now) --往前400年
	,@TheDateAfter400Years date = dateadd(year,400,@now) --往後400年
;

--得出最小十三號日期跟最晚十三號日期,最後算出有多少個13號
declare @MinDate date = 
	case 
		--需要特別判斷當天日期是否在十三號之前
		when day(@TheDateBefore400Years) <= 13 then convert(nvarchar(8),@TheDateBefore400Years,121) + '13'
		else convert(nvarchar(8),dateadd(month,1,@TheDateBefore400Years),121) + '13'
	end
	,@MaxDate date = 
	case 
		--需要特別判斷當天日期是否在十三號之後
		when day(@TheDateAfter400Years) >= 13 then convert(nvarchar(8),@TheDateAfter400Years,121) + '13'
		else convert(nvarchar(8),dateadd(month,-1,@TheDateAfter400Years),121) + '13'
	end
;

declare @DiffYears int = datediff(year,@MinDate,@MaxDate);

--統計13號出現在星期幾的總數
with cte as (
	select datepart(dw, convert(nvarchar(8),dateadd(month,val,@MinDate),121) + '13') week 
	from ( 
		--藉由spt_values + cross join得到多rows資料
		select row_number() over (order by(select 1)) - 1  val
		from master..spt_values T1,master..spt_values  T2
		where t1.type = 'P'
	) T
	where val <= @DiffYears * 12 - 1 
)
select 
	week dow 
	,count(1) count 
from cte 
group by week
order by week;

dow	count
1	1374
2	1370
3	1370
4	1374
5	1368
6	1376
7	1368