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

Last Reply 6 days ago By dharmendr

Posted 11 days ago

Hi,

 

How to display relevant values based upon login and logout times

I have 2 table

EmployeeTimes

Id Name logInTime Logouttime

1 a 3-6-2020 09:45:12 3-6-2020 12:23:34

1 a 3-6-2020 14:10:45 3-6-2020 18:34:20

1 a 5-6-2020 10:20:13 5-6-2020 13:23:45

1 a 5-6-2020 14:12:13 5-6-2020 14:47:10

2 b 3-6-2020 10:45:12 3-6-2020 12:35:34

2 b 3-6-2020 14:14:45 3-6-2020 18:04:20

2 b 5-6-2020 10:20:13 5-6-2020 13:23:45

2 b 5-6-2020 14:12:13 5-6-2020 16:47:10

EmployeePublicLeaves

LeaveId LeaveDate

1 04-06-2020

2 15-08-2020

Currently my requirement is that need to display o/p like below

Id Name Prsentdays absentdays genleaves Weekoffleaves Totaldays Paydays

1 a 1 2 1 2 6 4

2 b 2 1 1 2 6 5

Absentdays-Based upon date filters if there is no logintime present for that particular date consider that as absent day(in sample data I don't have data for 08-06-2020)

Note:Ex:04-06-2020 is a public holiday so don't consider that as absent day

Even though we don't have login and logouts times

genleaves-based upon from date and todate I am going to fetch how many leaves present in EmployeePublicLeaves

weekoffleaves-i am going to consider Saturday and Sunday as weekoff

In this example I have 2 weekoffs

Totaldays-Consider each date as oneday

Paydays-(Totaldays-Absentdays)

Posted 6 days ago

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

Id Name Absent Present Week Off
1 a 4 1 2