with date_range as (with recursive
t(n) as (
select date('2020-01-01')
union all
select n+1 from t where n <= current_date
)
select distinct to_char(t.n,'yyyy-mm-25')::date the_date from t)
select
case when position('A' in hr_division.code)=1 then '总公司' else coalesce(hr_division_trans.value,hr_division.name) end 分部,
to_char(date_range.the_date,'yyyy-mm') 月份,
coalesce(employee_type_trans.value,employee_type.name) 员工类型,
count(distinct emp.id) 人数
from date_range
left join hr_employee emp
left join emp_job on emp_job.employee_id=emp.id
on emp.hire_date<=date_range.the_date and date_range.the_date<=coalesce(emp.termination_date,current_date)
left join employee_type on emp_job.employee_type=employee_type.id
left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id
and employee_type_trans.name ='employee.type,name'
and employee_type_trans.lang='zh_CN'
left join hr_division on emp_job.division_id =hr_division.id
left join ir_translation hr_division_trans on hr_division_trans.res_id=hr_division.id
and hr_division_trans.name ='hr.division,name'
and hr_division_trans.lang='zh_CN'
where emp.active=True and
emp_job.eroad_index_id IS NOT NULL AND
emp_job.eroad_start_date <= date_range.the_date AND
(emp_job.eroad_end_date >= date_range.the_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 emp_job.employee_status=2
group by to_char(date_range.the_date,'yyyy-mm'),coalesce(employee_type_trans.value,employee_type.name),case when position('A' in hr_division.code)=1 then '总公司' else coalesce(hr_division_trans.value,hr_division.name) end
order by to_char(date_range.the_date,'yyyy-mm') asc
Change
Medium
