I want this report of employee register.
Date 1 2 3 4 Total Absent Present Leave
Emp1 A P P PL 4 1 2 1
Empi P A P P 4 1 3 0
i have two table
one is employee attendance (EmpID,INTIME,OUTTIME,ReportingDate,Status)
second is Employee Leaves (Empid,LeaveType(PL,CL,SL),StartDate,Endate,Noofday)
What I have tried:
First query is getting total of Present,Absent,HD,TDay in Employee Attendance table
SELECT 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 ]
FROM EmployeesAttendance
--WHERE (ReportingDate BETWEEN @StartDate AND @Enddate)
GROUP BY EmpID
Second Query is pivot table,converting employee attendance col into rows.
SELECT DISTINCT ReportingDate 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)
but i want that format which i mentioned above..please guide thanks....