Регистрация посещаемости для сотрудника ?

---

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 

у меня есть два стола

один сотрудник посещаемости (empid в,Интайм,OUTTIME,ReportingDate,статус)
второй-оставляет работника (empid в,LeaveType(ПЛ,КЛ,СЛ),начальная дата,Endate,Noofday)

Что я уже пробовал:

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 

Второй запрос-сводная таблица,преобразующая col посещаемости сотрудников в строки.

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)

но мне нужен тот формат, о котором я говорил выше.пожалуйста, направьте спасибо....

---