meidu2021/daju/target/classes/com/xcgl/reports/sql/XcglRptDao_activitiOvertimeStats.sql

select 
	realname,
	count(*) as overcount,
	case when max(overdaycount)=0 then 1 else max(overdaycount) end as maxdaycount
from (
		select
			 bu.realname as realName
			 ,hi.START_TIME_ + INTERVAL :tookTime day as shouldDoneTime
			 ,hivar.text_ as title,
			 case when hivardept.text_ is not null then hivardept.text_ else dept.glcdesc end as departname ,
			 proc.START_TIME_ as starttime,
			 hi.start_time_ as ustarttime,
			 datediff(now() , hi.start_time_) -:tookTime as overdaycount
		from
			ACT_HI_TASKINST hi
		left join  act_hi_varinst hivar on hi.PROC_INST_ID_ =hivar.PROC_INST_ID_ and hivar.NAME_ ='bpm_biz_title'
		left join  act_hi_varinst hivardept on hi.PROC_INST_ID_ =hivardept.PROC_INST_ID_ and hivardept.NAME_ ='dep_name'
		left join  act_hi_varinst hivarcreate on hi.PROC_INST_ID_ =hivarcreate.PROC_INST_ID_ and hivarcreate.NAME_ ='create_by'
		left join act_hi_procinst proc on proc.PROC_INST_ID_ = hivar.PROC_INST_ID_
		inner join t_s_base_user bu on hi.ASSIGNEE_=bu.username
		left join t_s_base_user startu on startu.username = proc.start_user_id_ and startu.delete_flag = 0
		left join t_s_user_org uo on uo.user_id = startu.id and uo.ifpluralism = 0
		left join t_s_depart dept on dept.id = uo.org_id
		left join t_bus_user_personnel per on per.userid=bu.id

		where per.leave_date is null
		and hi.START_TIME_<now() +INTERVAL 0-:tookTime day
		and hi.END_TIME_ is null
		and left(hi.START_TIME_ ,4) > '2019'
		and hivarcreate.text_ <> hi.ASSIGNEE_ ) temp 
group by realname
order by overcount desc