CLIENT SUPPORT » Bank Mega

CREATE PROCEDURE GetDurationLeave

(

	@landscape VARCHAR(20)

	,@EmpID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

	,@AbsType VARCHAR(20)	

)

AS

--DECLARE @landscape VARCHAR(20)  = '100'

--DECLARE @EmpID VARCHAR(20) = '19021633'

--DECLARE @StartDate VARCHAR(20)  = '20210316'

--DECLARE @EndDate VARCHAR(20)  = '20210318'

--DECLARE @AbsType VARCHAR(20)  = '1000'

DECLARE @WSType VARCHAR(20)

SELECT @WSType = ws_type

FROM hr_md_emp_md0025

WHERE emp_id = @EmpID AND start_date <= @EndDate AND end_date >= @EndDate

SELECT COUNT(cal.datespecified) AS duration FROM hr_tm_workschedule_calender AS cal 

LEFT JOIN hr_tm_day_type AS daytp

	On daytp.day_type = cal.day_type

WHERE cal.workschedule_type = @WSType AND daytp.flag = 'WORK'

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