KJL

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)