Hi mahesh213,

In your Leaves table user id is missing. So add it to compare for the user.

Refer below query.

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

CREATE TABLE #Leaves(LId INT,Id INT,LeaveType VARCHAR(50),days INT,Ldate DATE)
INSERT INTO #Leaves VALUES(1,1,'paid',1,'06-03-2020')
INSERT INTO #Leaves VALUES(2,1,'unpaid',1,'06-02-2020')

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),Total DECIMAL(18,2))

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',
            SUM(DATEDIFF(MINUTE,logInTime,Logouttime))
        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,0)
    END
    SET @From = DATEADD(DAY, 1, @From)
END
  
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 *,(Present+Paid+[Week Off]-Unpaid-Absent) Total 
				FROM 
				(
					SELECT Id,Name,'+@colsSum+',CONVERT(DECIMAL(5,2),SUM(Total) / 60) TotalHours,
					(SELECT days FROM #Leaves l WHERE l.Id = t.Id AND l.LeaveType = ''paid'') paid,
					(SELECT days FROM #Leaves l WHERE l.Id = t.Id AND l.LeaveType = ''unpaid'') unpaid
					FROM (
						SELECT Id,Name,' + @colsCount + ',Total
						FROM CTE 
						PIVOT(MAX(Status) FOR Status in (' + @cols + ')) p
						GROUP BY Id,Name,Total,'+@cols+') t
					GROUP BY Id,Name
				)a'           
EXECUTE(@query)

DROP TABLE #Leaves
DROP TABLE #Test
DROP TABLE #Date