Hi mahesh213,

Inside the else condition you need to check another condition with the Date column in the Holidays table.

If that Date present in Holidays table set the status with the Hiliday Name.

Refer below query.

SQL

CREATE TABLE #Holidays(HId INT,HolidayName VARCHAR(100),HolidayDate DATE)
INSERT INTO #Holidays VALUES(1,'Holiday','06-04-2020')
GO

CREATE TABLE #Test(Id INT,Name CHAR(1),logInTime DATETIME,Logouttime DATETIME)
INSERT INTO #Test VALUES(1,'a','6-3-2020 09:45:12','6-3-2020 12:23:34')
INSERT INTO #Test VALUES(1,'a','6-3-2020 14:10:45','6-3-2020 18:34:20')
INSERT INTO #Test VALUES(1,'a','6-5-2020 10:20:13','6-5-2020 13:23:45')
INSERT INTO #Test VALUES(1,'a','6-5-2020 14:12:13','6-5-2020 14:47:10')
 
DECLARE @From DATE, @To DATE
SET @From = '6-1-2020'
SET @To = '6-7-2020'
 
CREATE TABLE #Date (Id INT,Date DATE,Name CHAR(1),Status VARCHAR(10))
  
WHILE (@From <= @To)
BEGIN
    IF EXISTS(SELECT * FROM #Test WHERE CONVERT(DATE,logInTime) = CONVERT(DATE,@From))
    BEGIN
		INSERT INTO #Date
        SELECT Id,
            CONVERT(DATE,logInTime),
            Name,
            CASE WHEN SUM(DATEDIFF(SECOND,logInTime,Logouttime)) / 60 / 60 / 5 > 0 THEN 'Present'
            ELSE 'Absent'
            END 'Status'
        FROM #Test
        WHERE CONVERT(DATE,logInTime) = CONVERT(DATE,@From)
        GROUP BY Id,Name,CONVERT(DATE,logInTime)
    END
    ELSE
    BEGIN
        DECLARE @Status VARCHAR(10)
        SET @Status = 'Absent'
		IF EXISTS(SELECT * FROM #Holidays WHERE CONVERT(DATE,HolidayDate) = CONVERT(DATE,@From))
		BEGIN
			SET @Status = (SELECT HolidayName FROM #Holidays WHERE CONVERT(DATE,HolidayDate) = CONVERT(DATE,@From))
		END
        IF DATENAME(WEEKDAY, @From) = 'SATURDAY' OR DATENAME(WEEKDAY, @From) = 'SUNDAY'
            SET @Status = 'Week Off'
        ELSE
        DECLARE @Id INT, @Name CHAR(1)
        SELECT @Id = Id, @Name = Name FROM #Test
        INSERT INTO #Date VALUES(@Id,@From,@Name,@Status)
    END
    SET @From = DATEADD(DAY, 1, @From)
END
  
SELECT * FROM #Date
DROP TABLE #Test
DROP TABLE #Date
DROP TABLE #Holidays

Output