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

select realName,userid,username,month,concat(month,username) as id,sum(overtimetotal) as overtimetotal 
from (
select
	 bu.realname as realName,bu.id as userid, hi.ASSIGNEE_ as username,
	 left(hi.START_TIME_ + INTERVAL :tookTime day,7) as month ,count(*) as overtimetotal
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 
where
hi.START_TIME_<now() +INTERVAL 0-2 day
and hi.END_TIME_ is null
and left(hi.START_TIME_ ,4) > '2019'
and hivarcreate.text_ <> hi.ASSIGNEE_
and left(hi.START_TIME_ + INTERVAL :tookTime day,7) = :month
group by 
bu.realname ,bu.id , 
	 left(hi.START_TIME_ + INTERVAL :tookTime day,7) 
 union all
 
select
	 bu.realname as realName,bu.id as userid,  hi.ASSIGNEE_ as username,
	 left(hi.START_TIME_ + INTERVAL :tookTime day,7) as month ,count(*) as overtimetotal
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 
where
hi.START_TIME_< hi.END_TIME_  +INTERVAL 0-:tookTime day
and left(hi.START_TIME_ ,4) > '2019'
and hivarcreate.text_ <> hi.ASSIGNEE_
and left(hi.START_TIME_ + INTERVAL :tookTime day,7) = :month
group by 
bu.realname ,bu.id , 
	 left(hi.START_TIME_ + INTERVAL :tookTime day,7) 
	 ) temptable 
group by realName,userid,username,month
order by overtimetotal desc