How to merge Case when condition in pivot Table?

Question

 I want this report of employee register.

 

Date	1	2	3	4	Total	Absent	Present	Leave
Emp1	A	P	P	L	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)

 

i have two query now i want to merge them ,

First query is getting total of Present,Absent,HD,TDay  

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)