drop  FUNCTION getOnDutyDaysAfterExpectedDate;

create FUNCTION getOnDutyDaysAfterExpectedDate(v_expectedDate TIMESTAMP,v_employeeId varchar(100))
returns INT
   return
   (select
	    (sum(case when p.event_type=0 then 0-DATEDIFF(p.event_date_time,STR_TO_DATE('2000-01-01','%Y-%m-%d')) else DATEDIFF(p.event_date_time,STR_TO_DATE('2000-01-01','%Y-%m-%d')) end)
		  + (DATEDIFF(CURRENT_DATE,STR_TO_DATE('2000-01-01','%Y-%m-%d'))))
	  	as event_days
	 from
   (select x.event_type,(case when x.event_time>v_expectedDate then IFNULL(x.event_time,STR_TO_DATE('2000-01-01','%Y-%m-%d')) else v_expectedDate end) as event_date_time
	  from tenant_employee_events x where x.event_type in (0,1) and x.event_time<=CURRENT_TIMESTAMP and x.employee_id=v_employeeId
	 ) as p
	)