How employee leave days mentioned in attendance register ?

[akhter_1]

i have made leaves managment system ,now i want to mentioned leaves days in attendance register if employee take leave from 1-1-2019 to 4-1-2019 then 5 day display in attendance register as a (L) .
kindly guide me how it will get done.
here is my attendance register query in pivot table.

SELECT DISTINCT ReportingDate INTO #Dates
FROM EmployeesAttendance
WHERE     (ReportingDate BETWEEN @StartDate AND @Enddate)
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

-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

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 
'

here is my daily attendance INTIME and OutTime CTE query

;WITH Dates (ReportingDate) 
AS (
   SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
   UNION ALL
   SELECT DATEADD(day, 1, d.ReportingDate)
   FROM Dates d
   WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))

,CTE As
    (
    select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
    )
    ,CTE4 As
    (
    Select MachineAttendance.EmpID,MachineAttendance.MDate,MachineAttendance.INOUT from MachineAttendance
    ) 

    ,cte1 AS
    (
    SELECT CTE4.EmpID, CAST(CTE4.MDate as Date) AS [Date], 
    CASE WHEN CTE4.INOUT = 1 THEN CTE4.MDate END AS INOUT_INTIME,
    CASE WHEN CTE4.INOUT = 2 THEN CTE4.MDate END AS INOUT_OUTTIME
    From 
     CTE4

    ), 
   cte2 
    as
    (
    select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME, 
                          MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY EmpID, [Date]
    )
    select cte.EmpID,d.ReportingDate,cte2.Date,cte2.INTIME,  cte2.OUTTIME,  cte2.[Hours]
    , CASE WHEN  cte2.[Hours] >= 8 THEN 1
    WHEN  cte2.[Hours] = 0 THEN 0
    WHEN  cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
    CASE WHEN  cte2.[Hours] > CTE.Dhour then  cte2.[Hours] - CTE.Dhour else 0 End as OT,
    CASE when   
    cte.OTEntitled = 'Yes'  AND cte2.[Hours] >= CTE.Dhour 
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,  

    Convert(varchar(10), cte2.INTIME,108) as [Time],
   Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late ,
   Case when cte2.Hours >= cte.Dhour then 'P'  when cte2.Hours <= 6 then 'HD' else 'A'  end  as Status  
    from cte Cross Apply Dates d 
        Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
    order by cte.EmpID asc