select coalesce(dep_trans.value,hr_department.name) 部门, '待入职' 状态, coalesce(pre_hire_sub.pre_hires,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select pre_hire.department_id, count(distinct pre_hire.id) pre_hires from pre_hire where pre_hire.estimated_hire_date>current_date group by pre_hire.department_id) pre_hire_sub on pre_hire_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 晋升 union all select coalesce(dep_trans.value,hr_department.name) 部门, '晋升' 状态, coalesce(transfer_sub.jinsheng_count,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select job_transfer.department_id, sum(case when adjustment_type .code ='PAT001' then 1 else 0 end) jinsheng_count, sum(case when adjustment_type .code ='PAT004' then 1 else 0 end) jiangji_count from job_transfer left join adjustment_type on job_transfer.adjustment_type=adjustment_type.id where to_char(job_transfer.effective_date,'yyyy')='${filter_date:2021}' and job_transfer.x_bp_state='done' group by job_transfer.department_id) transfer_sub on transfer_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 降级 union all select coalesce(dep_trans.value,hr_department.name) 部门, '降级' 状态, coalesce(transfer_sub.jiangji_count,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select job_transfer.department_id, sum(case when adjustment_type .code ='PAT001' then 1 else 0 end) jinsheng_count, sum(case when adjustment_type .code ='PAT004' then 1 else 0 end) jiangji_count from job_transfer left join adjustment_type on job_transfer.adjustment_type=adjustment_type.id where to_char(job_transfer.effective_date,'yyyy')='${filter_date:2021}' and job_transfer.x_bp_state='done' group by job_transfer.department_id) transfer_sub on transfer_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 未获得从业资格 union all select coalesce(dep_trans.value,hr_department.name) 部门, '未获得从业资格' 状态, coalesce(zgz_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join ( select emp_job.department_id, count(distinct emp.id) count_emp from hr_employee emp left join emp_job on emp.job_info_id=emp_job.id left join x_builder_practitioner_qualification on x_builder_practitioner_qualification.x_employee=emp.id and to_char(x_builder_practitioner_qualification.x_getfunds_date,'yyyy')='${filter_date:2021}' and x_builder_practitioner_qualification.active=True left join hr_department on emp_job.department_id=hr_department .id where emp_job.employee_status=2 and x_builder_practitioner_qualification.x_employee is null group by emp_job.department_id ) zgz_sub on zgz_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' ================= select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '在职人数' 类型, coalesce(total_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select emp_job.department_id, count(distinct emp.id) count_emp from hr_employee emp left join emp_job on emp.job_info_id=emp_job.id where emp_job.employee_status=2 and emp.active=True group by emp_job.department_id) total_sub on total_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 年初人数 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '年初人数' 类型, coalesce(total_begin.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select emp_job.department_id, count(distinct emp.id) count_emp from hr_employee emp left join emp_job on emp.id=emp_job.employee_id where emp_job.employee_status=2 and emp.active=True and emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <=date ('${filter_date:2021}'||'-01-01') AND (emp_job.eroad_end_date >=date ('${filter_date:2021}'||'-01-01') OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL group by emp_job.department_id) total_begin on total_begin.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 入职人数 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '入职人数' 类型, coalesce(hire_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select emp_job.department_id, count(distinct emp.id) count_emp from hr_employee emp left join emp_job on emp.id=emp_job.employee_id where emp.active=True and emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <=emp.hire_date AND (emp_job.eroad_end_date >=emp.hire_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and to_char(emp.hire_date,'yyyy')='${filter_date:2021}' group by emp_job.department_id) hire_sub on hire_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 离职 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '离职人数' 类型, coalesce(leave_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select emp_job.department_id, count(distinct emp.id) count_emp from hr_employee emp left join emp_job on emp.id=emp_job.employee_id where emp.active=True and emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <=emp.termination_date AND (emp_job.eroad_end_date >=emp.termination_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and to_char(emp.termination_date ,'yyyy')='${filter_date:2021}' group by emp_job.department_id) leave_sub on leave_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 调岗调薪调出 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '调出人数' 类型, coalesce(job_transfer_out_sub.count_emp,0)+coalesce(job_adjustment_out_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join ( select job_transfer.department_id, count(distinct job_transfer.employee_id) count_emp from job_transfer where job_transfer.x_bp_state='done' and to_char(job_transfer.effective_date,'yyyy')='${filter_date:2021}' and job_transfer.department_id<> job_transfer.new_department_id group by job_transfer.department_id) job_transfer_out_sub on job_transfer_out_sub.department_id=hr_department.id – 岗位调整调出 left join (select job_adjustment.department_id, count(distinct job_adjustment.employee_id) count_emp from job_adjustment where job_adjustment.x_bp_state='done' and to_char(job_adjustment.effective_date,'yyyy')='${filter_date:2021}' and job_adjustment.department_id<> job_adjustment.new_department_id group by job_adjustment.department_id) job_adjustment_out_sub on job_adjustment_out_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 调岗调薪调入 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '调入人数' 类型, coalesce(job_transfer_in_sub.count_emp,0)+coalesce(job_adjustment_in_sub.count_emp,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join ( select job_transfer.new_department_id, count(distinct job_transfer.employee_id) count_emp from job_transfer where job_transfer.x_bp_state='done' and to_char(job_transfer.effective_date,'yyyy')='${filter_date:2021}' and job_transfer.department_id<> job_transfer.new_department_id group by job_transfer.new_department_id) job_transfer_in_sub on job_transfer_in_sub.new_department_id=hr_department.id – 岗位调整调入 left join (select job_adjustment.new_department_id, count(distinct job_adjustment.employee_id) count_emp from job_adjustment where job_adjustment.x_bp_state='done' and to_char(job_adjustment.effective_date,'yyyy')='${filter_date:2021}' and job_adjustment.department_id<> job_adjustment.new_department_id group by job_adjustment.new_department_id) job_adjustment_in_sub on job_adjustment_in_sub.new_department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active' – 人工成本 union all select coalesce(dep_trans.value,hr_department.name) 部门, '${filter_date:2021}' 年份, '总人工成本' 类型, coalesce(rgcb_sub.total_amount,0) 人数 from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' left join (select hr_department .id department_id, sum(payroll_ledger.Basic_monthly_salary::float+payroll_ledger.Welfare::float+payroll_ledger.Welfare2::float+payroll_ledger.Welfare3::float+payroll_ledger.bonus::float+person_training_information.x_fee::float) total_amount from payroll_ledger left join hr_employee emp on payroll_ledger.employee_number=emp.employee_number left join person_training_information on person_training_information.person_id=emp.person_id and to_char(person_training_information.start_date,'yyyy')='${filter_date:2021}' left join hr_department on hr_department.code=payroll_ledger.Department where substring(payroll_ledger.payroll_period::varchar from 1 for 4)='${filter_date:2021}' group by hr_department .id) rgcb_sub on rgcb_sub.department_id=hr_department.id where hr_department.eroad_index_id is null and hr_department.status='active'