Employee Register using sql server to present,absent,holiday of each employee-CodePudding

Table 1: Table I had for example one employee data

EmployeeID	companyID	process date	DayStatus
1001	52	2022-01-01	X
1001	52	2022-01-02	X
1001	52	2022-01-03	X
1001	52	2022-01-04	H
1001	52	2022-01-05	/

Table 2: Need to show like this for multiple employees

EmployeeID	companyID	D1	D2	D3	D4	D5...	D31	Total Present	Total Holidays	Total absent
1001	52	X	X	X	X	X	X	26	4	0
1002	52	A	X	X	X	X	X	25	4	1
1003	52	A	X	/	X	X	X	24.5	4	1

I want this type table using SQL query.

I have employeeAttendatance table 1 and show data like table 2

CodePudding user response：

You should use sql pivot. But there is a trick with the date columns. If they where static that would be no problem and you can do that like this:

SELECT * from
(SELECT EmployeeID,CompanyID,[process date],DayStatus FROM dbo.Employee )
AS source
PIVOT
(
 Max(DayStatus) 
  FOR [process date] IN ( [2022-01-01],[2022-01-02],[2022-01-03],[2022-01-04],[2022-01-05])
)AS PivotTable

But if they are dynamic you should combine the solution with dynamic sql:

DECLARE @cols AS NVARCHAR(MAX)
        ,@query AS NVARCHAR(MAX);

    SET @cols = STUFF((
                SELECT ','   QUOTENAME(c.[process date])
                FROM (
                    SELECT DISTINCT [process date]
                    FROM Employee
                    ) c
                ORDER BY [process date]
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
                SELECT @cols


SET @query = N'SELECT * from
(SELECT EmployeeID,CompanyID,[process date],DayStatus FROM dbo.Employee )
AS source
PIVOT
(
 Max(DayStatus) 
  FOR [process date] IN ( ' @cols ')
)AS PivotTable';
EXECUTE (@query)