SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION dbo.GetTotalWeekHoliday
    (
		@year SMALLINT,
        @Month TINYINT,
        @joindate DATE,
		@DayName VARCHAR(10)
    )
RETURNS TABLE
AS
RETURN
(	WITH Dates AS
	(	SELECT	Date = DATEADD(DAY ,ROW_NUMBER() OVER(ORDER BY n1.N) - 1, ms.MonthStart),ms.MonthStart		
		FROM	(VALUES (DATEFROMPARTS(@year, @month, 1))) AS ms (MonthStart)
		-- Cross join to 8x4 rows (32) to ensure full month
		CROSS JOIN (VALUES (1), (1), (1), (1)) AS n1 (N)
		CROSS JOIN (VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS n2 (N)
	)
	SELECT	Holidays = COUNT(*)
	FROM	Dates AS d
	WHERE	DATENAME(WEEKDAY, d.Date) = @DayName
	AND		d.Date >=ISNULL(@JoinDate, '19000101')
	AND		d.Date < EOMONTH(d.MonthStart)
)
SELECT *
FROM (VALUES 
  (1, 2024, '20231223'), 
  (1, 2024, '20240120')
  ) as d (Month, Year, StartDate)
CROSS APPLY dbo.GetTotalWeekHoliday(d.Year, d.Month, d.StartDate,'Tuesday') AS x


Month
Year
StartDate
Holidays


1
2024
20231223
5

1
2024
20240120
2


SELECT *,
    Holidays = (SELECT h.Holidays FROM dbo.GetTotalWeekHoliday(d.Year, d.Month, d.StartDate,'Tuesday') AS h)
FROM (VALUES 
    (1, 2024, '20231223'), 
    (1, 2024, '20240120')
    ) as d (Month, Year, StartDate);


Month
Year
StartDate
Holidays


1
2024
20231223
5

1
2024
20240120
2