DECLARE @MONTHS varchar(10) = '${month}'

DECLARE @FullDate DATE = CAST(@MONTHS + '-01' AS DATE)

DECLARE @Month int = cast(MONTH(@FullDate) as int)

select * from (

select aa.fd_emp_id as fdEmpId,aa.fd_login_name as fdLoginName,

aa.fd_name as fdName,aa.dep_name,aa.depts_name as deptsName,

sum(aa.total_hour) as totalHour,count(*) as flowCount,month,

DATEFROMPARTS(YEAR(GETDATE()), @Month, 1) AS startTime,

EOMONTH(DATEFROMPARTS(YEAR(GETDATE()), @Month, 1)) AS endTime

from (

select a.fd_emp_id,b.fd_login_name,b.fd_name,f.dep_name,d.depts_name,

sum(a.fd_day) as fd_day,sum(a.fd_day)*8 as total_hour,c.fd_id,

convert(nvarchar(10),min(a.fd_start_time),120) as start_time,

convert(nvarchar(10),max(a.fd_plan_time),120) as end_time,

c.doc_subject,c.fd_number,convert(nvarchar(7),min(a.fd_start_time),120) as month

from ekp_it0012_detail a

left join view_person_info b on a.fd_emp_id=b.fd_id

left join km_review_main c on a.fd_parent_id=c.fd_id

left join view_sf_employee d on b.fd_login_name=d.employee_code

left join ekp_it0012 e on a.fd_parent_id=e.fd_id

left join view_sf_dep f on d.dep_id=f.dep_id

where c.doc_status in ('20') and b.depts_name like '%知识共享中心%' 

and convert(nvarchar(10),a.fd_start_time,120) >='2023-09-01'

group by a.fd_emp_id,b.fd_login_name,b.fd_name,f.dep_name,d.depts_name,c.fd_id,

c.doc_subject,c.fd_number

) as aa

group by aa.fd_emp_id,aa.fd_login_name,aa.fd_name,aa.depts_name,aa.dep_name,month)z

where z.month=@MONTHS

${if(len(comboCheckBox0)==0,"","and z.fdName in ('"+comboCheckBox0+"')")} 

order by z.dep_name desc,z.totalHour desc