SELECT DISTINCT ReportingDate, SUM(CASE WHEN status = 'P' THEN 1 WHEN status = 'HD' THEN 0.5 WHEN status = 'A' THEN 0 END) AS [T.P], SUM(CASE WHEN status = 'A' THEN 1 WHEN status = 'HD' THEN 0.5 END) AS [A], SUM(CASE WHEN status = 'P' THEN 1 WHEN status = 'HD' THEN 1 WHEN status = 'A' THEN 1 END) AS [TDay ] INTO #Dates FROM EmployeesAttendance ORDER BY ReportingDate DECLARE @cols NVARCHAR(4000) SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112) + ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']') FROM #Dates ORDER BY ReportingDate DECLARE @qry NVARCHAR(4000) = N'SELECT * FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status, DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate FROM EmployeesAttendance Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )emp PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat ' -- Executing the query EXEC(@qry)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)