How to merge Case when condition in pivot Table?

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)

You need to merge your two queries to include leave data from the EmployeeLeaves table into your pivot table while maintaining the total calculations for Present (P), Absent (A), and Half-Day (HD).

-- Step 1: Create a temporary table to hold attendance and leave data
WITH CombinedData AS (
    -- Attendance Data
    SELECT 
        EA.EmpID, 
        EA.ReportingDate, 
        EA.Status 
    FROM EmployeesAttendance EA

    UNION ALL

    -- Leave Data expanded into daily records
    SELECT 
        EL.EmpID, 
        DATEADD(DAY, n.number, EL.StartDate) AS ReportingDate, 
        EL.LeaveType 
    FROM EmployeeLeaves EL
    JOIN master.dbo.spt_values n 
        ON n.type = 'P' AND n.number < DATEDIFF(DAY, EL.StartDate, EL.EndDate) + 1
)

-- Step 2: Generate Dynamic Columns for Pivot
SELECT DISTINCT ReportingDate INTO #Dates FROM CombinedData ORDER BY ReportingDate

DECLARE @cols NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',[' + CONVERT(VARCHAR, DATEPART(DAY, ReportingDate), 112) + ']', 
                        '[' + CONVERT(VARCHAR, DATEPART(DAY, ReportingDate), 112) + ']') 
FROM #Dates 
ORDER BY ReportingDate

-- Step 3: Construct the Pivot Query
DECLARE @qry NVARCHAR(MAX) = 
N'SELECT EmpID, ' + @cols + 
N', SUM(CASE WHEN Status = ''P'' THEN 1 WHEN Status = ''HD'' THEN 0.5 ELSE 0 END) AS Present,
SUM(CASE WHEN Status = ''A'' THEN 1 WHEN Status = ''HD'' THEN 0.5 ELSE 0 END) AS Absent,
SUM(CASE WHEN Status IN (''PL'', ''CL'', ''SL'') THEN 1 ELSE 0 END) AS Leave
FROM ( 
    SELECT EmpID, Status, DATEPART(DAY, ReportingDate) AS DDate FROM CombinedData
) emp
PIVOT (
    MAX(Status) FOR DDate IN (' + @cols + ')
) AS stat
GROUP BY EmpID'

-- Step 4: Execute the Query
EXEC(@qry)