Calculate Days Present and absent from table in SQL Server

Hi mahesh213,

Refer below query and set the Absent according to your calculation.

SQL 

CREATE TABLE #Test(Id INT,Name CHAR(1),logInTime DATETIME,Logouttime DATETIME)
INSERT INTO #Test VALUES(1,'a','3-6-2020 09:45:12','3-6-2020 12:23:34')
INSERT INTO #Test VALUES(1,'a','3-6-2020 14:10:45','3-6-2020 18:34:20')
INSERT INTO #Test VALUES(1,'a','5-6-2020 10:20:13','5-6-2020 13:23:45')
INSERT INTO #Test VALUES(1,'a','5-6-2020 14:12:13','5-6-2020 14:47:10')
INSERT INTO #Test VALUES(2,'b','3-6-2020 10:45:12','3-6-2020 12:35:34')
INSERT INTO #Test VALUES(2,'b','3-6-2020 14:14:45','3-6-2020 18:04:20')
INSERT INTO #Test VALUES(2,'b','10-6-2020 10:20:13','10-6-2020 13:23:45')
INSERT INTO #Test VALUES(2,'b','10-6-2020 14:12:13','10-6-2020 16:47:10')

;WITH CTE AS( 
	SELECT 
            Id,
            Name,
            SUM(DATEDIFF(SECOND,logInTime,Logouttime)) / 60 / 60 / 5 'Days'
	FROm #Test
	GROUP BY Id,Name,CONVERT(DATE,logInTime)
) 
SELECT DISTINCT ID,
		Name,
		(SELECT COUNT(Days) FROM CTE t1 WHERE t1.Id = t.Id AND t1.Days > 0) 'PresentDays',
		(SELECT COUNT(Days) FROM CTE t1 WHERE t1.Id = t.Id AND t1.Days = 0) 'AbsentDays' 
FROM CTE t
 
DROP TABLE #Test

Output