How to get Day and Day Name with assigning Sunday as Holiday in SQL Server

Refer Below Test Query for your reference.

/*Declaration of Month , Year , Date and TotalDays */
DECLARE @Month INT
DECLARE @Year INT
DECLARE @Date DATE
DECLARE @TotalDays INT

-- Set Month And Year Value
SET @Month = 09
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')

-- 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 UPPER(DATENAME(dw,DATEADD(DD,@Counter - 1,@Date))) = UPPER('SUNDAY')
				   THEN 'Holiday'
				   ELSE (SELECT EmployeeName
						 FROM (SELECT ROW_NUMBER() OVER ( ORDER BY EmployeeId DESC) 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')
			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