ALTER PROCEDURE CKJLTMTOPYGETABSENCE
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '21120023'
--DECLARE @StartDate VARCHAR(20) = '20221101'
--DECLARE @EndDate VARCHAR(20)  = '20221131'

SELECT DISTINCT CASE WHEN COUNT(wscal.DateSpecified) > 0 THEN COUNT(wscal.DateSpecified) ELSE 0 END  AS TotAbsence
FROM PHRPA0018 AS pa18
LEFT JOIN dbo.PHRPA0025 AS pa25
	ON pa25.EmployeeID = pa18.EmployeeID
	AND pa25.StartDate <= @EndDate AND pa25.EndDate >= @EndDate
LEFT JOIN dbo.PHRTMWSCAL AS wscal
	ON wscal.WorkScheduleType = pa25.WorkScheduleType
LEFT JOIN dbo.PHRTMDAYTYP AS daytype
	ON wscal.DayType = daytype.DayType
WHERE (pa18.EmployeeID = @EmployeeID OR @EmployeeID = '')
	AND wscal.DateSpecified BETWEEN pa18.StartDate AND pa18.EndDate
	AND daytype.Flag <> 'OFF'
	AND (pa18.StartDate BETWEEN @StartDate AND @EndDate OR pa18.EndDate BETWEEN @StartDate AND @EndDate)
	AND (wscal.DateSpecified BETWEEN @StartDate AND @EndDate)