Get Day and Day Name with Holidays in SQL Server

Refer the below Test Query for your reference.

DECLARE @Month INT
DECLARE @Year INT
DECLARE @Date DATE
DECLARE @TotalDays INT
 
-- Set Month And Year Value
SET @Month = 10
SET @Year  = 2016
 
-- SET Default Date by month and year
SET @Date = CAST(RIGHT('0' + CAST(@Month as VARCHAR(2)), 2)+'-'+'01-'+ CAST(@Year as VARCHAR(4)) As DATE)
-- To Get Total days
SET @TotalDays = datediff(day, @Date, dateadd(month, 1, @Date))
 
-- Details table
DECLARE @Details AS TABLE([DATE] DATE,[DayName] VARCHAR(20),EmployeeName_Holdiday VARCHAR(15))
 
-- Table where Employee Details is stored
DECLARE @Table AS TABLE(EmployeeId INT,EmployeeName VARCHAR(20))
INSERT INTO @Table VALUES(1,'David')
INSERT INTO @Table VALUES(2,'Kevin')
INSERT INTO @Table VALUES(3,'Peter')
INSERT INTO @Table VALUES(4,'Jhon')
INSERT INTO @Table VALUES(5,'Andrea')

DECLARE @HolidayTable AS TABLE(HolidayDate date,Holidayname VARCHAR(20))
INSERT INTO @HolidayTable VALUES('10/11/2016','DASSERA')
INSERT INTO @HolidayTable VALUES('10/12/2016','MUHARRAM')
INSERT INTO @HolidayTable VALUES('10/31/2016','DIWALI')
-- SELECT * FROM @HolidayTable

-- To get total Employee count
DECLARE @TotalEmployeeCount INT
SET @TotalEmployeeCount = (SELECT COUNT(EmployeeId) FROM @Table)
       
      -- Counter for While loop as well as for EmployeeCount Checking
      DECLARE @Counter INT
      Declare @EmployeeCounter INT
 
      SET @Counter = 1   
      SET @EmployeeCounter = 1
         
      WHILE @Counter<= @TotalDays
      BEGIN
      -- INSERT IN TO Temp table @Details
      INSERT INTO @Details
      VALUES (CAST(RIGHT('0' + CAST(@Month as VARCHAR(2)), 2)+'-'+ CAST(@Counter as VARCHAR(2))  +'-'+ CAST(@Year as VARCHAR(4)) As DATE)
             ,DATENAME(dw,DATEADD(DD,@Counter - 1,@Date))
             ,CASE WHEN EXISTS(SELECT HolidayDate FROM @HolidayTable WHERE CAST(HolidayDate as DATE) = CAST(DATEADD(DD,@Counter - 1,@Date) as DATE))
                   THEN (SELECT Holidayname FROM @HolidayTable WHERE CAST(HolidayDate as DATE) = CAST(DATEADD(DD,@Counter - 1,@Date) as DATE))			 
				   WHEN UPPER(DATENAME(dw,DATEADD(DD,@Counter - 1,@Date))) = UPPER('SUNDAY')
                   THEN 'Holiday'				   
                   ELSE (SELECT EmployeeName
                         FROM (SELECT ROW_NUMBER() OVER ( ORDER BY EmployeeId) as RowNumber
                          ,EmployeeId
                          ,EmployeeName
                         FROM @Table) tbl
                         WHERE tbl.RowNumber = @EmployeeCounter)
                    END)
       -- CHECK FOR SUNDAY Also Increament only for Employee If its not sunday
        IF  UPPER(DATENAME(dw,DATEADD(DD,@Counter - 1,@Date))) <> UPPER('SUNDAY') AND NOT EXISTS(SELECT HolidayDate FROM @HolidayTable WHERE CAST(HolidayDate as DATE) = CAST(DATEADD(DD,@Counter - 1,@Date) as DATE))		
            BEGIN
				if @EmployeeCounter < @TotalEmployeeCount
					BEGIN
						SET @EmployeeCounter += 1
					END
				ELSE
					BEGIN
						SET @EmployeeCounter = 1
					END
			END 			 
        SET @Counter = @Counter + 1
      END
       
SELECT REPLACE(CONVERT(VARCHAR(24),CAST([DATE] AS DATE),103) ,'/','-')[Date]
       ,[DayName]
       ,EmployeeName_Holdiday
FROM @Details

OUTPUT