Attendance register for employee ?

See more:

SQL

SQL-Server-2008R2

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....