Calculate Present and absent from table in matrix form in SQL Server

Hi mahesh213,

Refer below query and handle the Total from code behind.

SQL

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 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

DECLARE @cols AS NVARCHAR(MAX),@colsCount AS NVARCHAR(MAX),@colsSum AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Status) 
FROM #Date
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @colsCount = STUFF((SELECT DISTINCT ',COUNT(' + QUOTENAME(Status) +') AS ' + QUOTENAME(Status)
FROM #Date
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @colsSum = STUFF((SELECT DISTINCT ',SUM(' + QUOTENAME(Status) +') AS ' + QUOTENAME(Status)
FROM #Date
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = ';WITH CTE AS ( SELECT * FROM #Date)
                SELECT Id,Name,'+@colsSum+'
				FROM (
					SELECT Id,Name,' + @colsCount + ' 
					FROM CTE   
					PIVOT(MAX(Status) FOR Status in (' + @cols + ')) p
					GROUP BY Id,Name,'+@cols+') t 
				GROUP BY Id,Name' 				 
EXECUTE(@query)

DROP TABLE #Test
DROP TABLE #Date

Output