SELECT Isnull(( CASE
WHEN provide_region BETWEEN 1000 AND 1999 THEN '公司内部门-' + option_name
WHEN provide_region BETWEEN 2000 AND 2999 THEN '公司外人员-' + provide.name
WHEN provide_region BETWEEN 3000 AND 3999 THEN '分公司-' + option_name
END ), '') AS provide_region,
datepart_month,
per_count
FROM (SELECT Count(id) AS per_count,
personnel_provide_id,
Datepart(month, returned_date) AS datepart_month
FROM yh_personnel
WHERE yh_personnel.personnel_type&60 = 32
AND returned_date BETWEEN '2020-1-1'AND '2020-12-31'
GROUP BY personnel_provide_id,
Datepart(month, returned_date)) AS tb_base
LEFT JOIN yh_personnel_provide AS provide
ON tb_base.personnel_provide_id = provide.id
LEFT JOIN yh_select_option
ON option_id = provide.provide_region
AND option_tags = 'region'
WHERE personnel_provide_id != 0
AND datepart_month != 0
SELECT (SELECT option_name
FROM yh_select_option
WHERE option_id = P_yq
AND option_tags = 'PTYQ') AS subject,
Count(CASE
WHEN department = 11 THEN 1
END) AS channel_expansion_count,
Count(CASE
WHEN department = 14 THEN 1
END) AS channel_maintenance_count,
Count(CASE
WHEN department = 12 THEN 1
END) AS business_count,
Count(CASE
WHEN department = 13 THEN 1
END) AS project_count,
Count(CASE
WHEN department = 15 THEN 1
END) AS business_support_count,
Count(CASE
WHEN department = 16 THEN 1
END) AS assets_manager_count,
Count(CASE
WHEN department = 17 THEN 1
END) AS clearing_count,
count(case when P_jd=4 then 1 end ) as invalid_count,
'/' as timeout_count,
yh_manager.*
FROM yh_ps_demand_application
LEFT JOIN yh_manager
ON yh_manager.id = yh_ps_demand_application.P_fqr
group by P_yq
