KJL

--ALTER PROCEDURE [dbo].[GetTotalTaken]

--    (

--      @EmployeeID VARCHAR(20),

--	   @StartDate VARCHAR(20),

--	    @EndDate VARCHAR(20)

--    )

--AS 

DECLARE @EmployeeID AS VARCHAR(200) ='30000134'

DECLARE @StartDate AS VARCHAR(200) ='20221006'

DECLARE @EndDate AS VARCHAR(200) ='20221006'

DECLARE @jumlah AS VARCHAR(200) ='0'

SET @jumlah = (SELECT COUNT(DAYTYP.DayType) AS jumlah FROM PHRPA0025 pa25 LEFT OUTER JOIN 

dbo.PHRPA0125 pa125 ON pa25.EmployeeID = pa125.EmployeeID  LEFT OUTER JOIN 

dbo.PHRTMDAYTYP DAYTYP ON pa125.DayType = DAYTYP.DayType AND DAYTYP.Flag='WORK'

WHERE pa25.EmployeeID = @EmployeeID 

			AND ( pa25.StartDate <= @StartDate

              AND pa25.EndDate >= @EndDate

            )

			AND ( pa125.EndDate BETWEEN @StartDate AND @EndDate)

			AND DAYTYP.DayType IS not NULL)

IF @jumlah = '0'

BEGIN

SET @jumlah = (SELECT COUNT(DAYTYP.DayType) AS jumlah FROM PHRPA0025 pa25 LEFT OUTER JOIN 

dbo.PHRTMWSCAL cal ON pa25.WorkScheduleType = cal.WorkScheduleType  LEFT OUTER JOIN 

dbo.PHRTMDAYTYP DAYTYP ON cal.DayType = DAYTYP.DayType AND DAYTYP.Flag='WORK'

WHERE pa25.EmployeeID = @EmployeeID

			AND ( pa25.StartDate <= @StartDate

              AND pa25.EndDate >= @EndDate

            )

			AND ( cal.DateSpecified BETWEEN @StartDate AND @EndDate ) 

			--AND cal.[Year] = YEAR(GETDATE())

			AND (cal.[Year]  =  YEAR(@StartDate) or cal.[Year]  =   YEAR(@EndDate))

		   --AND (cal.[Year]  <=  YEAR(@StartDate) and cal.[Year] >=  YEAR(@EndDate))

			AND DAYTYP.DayType IS not NULL)

end

SELECT @jumlah AS jumlah