project_group/leading-project
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
leading-project/finereport/kanban/pc/总裁.sql

881 lines
30 KiB
MySQL
Raw Permalink Normal View History

冲突
2024-11-27 11:07:58 +08:00
select
concat(right(ym,2) + 0 , '月') ym
, sum(plan_num) plan_num
, sum(plan_complete_num) plan_complete_num
, sum(plan_incomplete_num) plan_incomplete_num
, sum(plan_complete_num) / sum(plan_num) plan_complete_rate
, sum(plan_adjust_num) plan_adjust_num
, sum(plan_adjust_num) / sum(plan_num) plan_adjust_rate
from dws_operation_plan_summary_m a
left join (select distinct organ_code, organ_name, comm_id, comm_name
, oa_organ_code, oa_organ_name, oa_comm_id, oa_comm_name from dim_organ_mapping where comm_id is not null) b
on a.subcompanyid = b.oa_comm_id
where 1=1
${if(len(organ)=0,"","and organ_code = '"+organ+"'")}
and left(ym,4) = '2024'
group by ym
order by ym asc
-- 【设备巡检】
-- 【old】
select
sum(task_finish_num)
, sum(task_num)
, sum(task_finish_num) / sum(task_num) inspection_finish_rate
FROM dws_engine_equipment_inspect_task_m
where task_ym = date_format(current_date(),'%Y%m')
${if(len(organ)=0,"","and organ_code = '"+organ+"'")}
;
-- 【new】
select
comm_id
,comm_name
,task_ym
sum(task_finish_num) / sum(task_num) as inspection_finish_rate
FROM dws_engine_equipment_inspect_task_m
where task_ym = date_format(current_date(),'%Y%m')
group by
comm_id
,comm_name
,task_ym
;
${if(len(organ)=0,"","and organ_code = '"+organ+"'")}
设备巡查.select(task_finish_num) / 设备巡查.select(task_num)
-- ----------------------------------
-- report_商业出租率
-- ---------------------------------
select sum(lease_area)/sum(construction_area) rent_rate
FROM dws_business_rent_rate_d a
left join (select distinct id, erp_id from ods_bs_project_d) b
on a.project_id = b.id
left join (select distinct organ_code, organ_name, comm_id, comm_name from dim_organ_mapping where comm_id is not null) c
on b.erp_id = c.comm_id
where 1=1
${if(len(organ)=0,"","and c.organ_code = '"+organ+"'")}
-- ----------------------------------
-- report_商业出租率_new
-- ---------------------------------
SELECT
T.`type`
,SUM(T.rentRoomAreaSum) / (SUM(T.rentRoomAreaSum) + SUM(T.notRentRoomAreaSum)) as rent_rate -- 出租率
FROM
(
SELECT
B.`type`
,SUM(CASE WHEN A.business_status = 1 THEN A.construction_area ELSE 0 END ) AS rentRoomAreaSum -- 出租面积
,SUM(CASE WHEN A.business_status != 4 AND A.business_status != 1 THEN A.construction_area ELSE 0 END ) AS notRentRoomAreaSum -- 未出租面积
FROM
(
SELECT
id
,erp_id
,building_id
,business_status
,construction_area -- 建筑面积
FROM dw.ods_bs_room_d -- 商管房间表
WHERE del_flag = 0
AND enable_flag = 0
AND review_status = 2
)A
LEFT JOIN
(
select
id
,project_id
,project_name
,`type` -- 业态 商业街，集中商业，写字楼，底商
from dim_business_building_d
)B
on A.building_id = B.id
LEFT JOIN
(
select
distinct
organ_code
,organ_name
,comm_id
,comm_name
from dim_organ_mapping
where comm_id is not null
)C
on A.erp_id = C.comm_id
where 1=1
${if(len(organ)=0,"","and b.organ_code = '"+ organ+"'")}
GROUP BY
B.`type`
)T
GROUP BY
T.`type`
;
-- ----------------------------------
-- report_商业收缴率
-- ---------------------------------
select sum(received_money) / sum(current_sure_money) collection_rate
FROM dws_business_bill_m a
left join (select distinct id, erp_id from ods_bs_project_d) b
on a.project_id = b.id
left join (select distinct organ_code, organ_name, comm_id, comm_name from dim_organ_mapping where comm_id is not null) c
on b.erp_id = c.comm_id
where left(accrual_month,7) = '2024-06'
${if(len(organ)=0,"","and c.organ_code = '"+organ+"'")}
;
-- ----------------------------------
-- report_商业收缴率_new
-- ---------------------------------
SELECT
SUM(T.received_money) / SUM(T.current_sure_money) AS collection_rate -- 收缴率
FROM
(
SELECT
T1.received_money AS received_money
,T1.current_sure_money AS current_sure_money
FROM
(
SELECT
a.project_id -- 项目id 关联bs_project表
,SUM(a.received_money) AS received_money -- 实收金额
,SUM(a.current_sure_money) AS current_sure_money -- 实际应收金额=应收金额-合同减免金额
-- ,SUM(a.under_money) AS under_money -- 欠收金额
FROM
(
SELECT
contract_id
,accrual_month
,start_period
,bill_type
,received_money -- 实收金额
,current_sure_money -- 实际应收金额=应收金额-合同减免金额
,current_sure_money - received_money as under_money -- 欠收金额
,project_id
FROM dw.ods_bs_fi_bills_d
WHERE save_status = 1 -- 账单状态 0是保存 1是审核过的
-- ${if(len(project) == 0,"","AND project_id in (" + project + ")")}
)a
left join
(
SELECT
status
,stop_date
,id
FROM dw.ods_bs_contract_d
WHERE del_flag = 0
)bc
on bc.id = a.contract_id
WHERE ((bc.status != 2 and bc.stop_date is null) OR
(bc.stop_date is not null
and ((a.accrual_month <= DATE_FORMAT(bc.stop_date, '%Y-%m')
and a.start_period <= bc.stop_date)
or a.bill_type = 15)
))
group by
a.project_id
UNION ALL
SELECT
a.project_id
,SUM(b.received_amount) AS received_money
,SUM(b.received_money) AS current_sure_money
-- ,SUM(b.under_money) AS under_money
FROM
(
SELECT
receive_no -- 催缴单号
,project_id
FROM dw.ods_bs_fi_receive_other_d
where recheck_status = 2
)a
LEFT JOIN
(
SELECT
receive_no
,received_amount
,received_money
,received_money - received_amount AS under_money
FROM dw.ods_bs_fi_receive_other_item_d
)b
ON a.receive_no = b.receive_no
group by
project_id
)T1
left join
(
select
distinct id
,erp_id
from dw.ods_bs_project_d
) b
on T1.project_id = b.id
left join
(
select
distinct
organ_code
,organ_name
,comm_id
,comm_name
from dim_organ_mapping
where comm_id is not null
) c
on b.erp_id = c.comm_id
${if(len(organ)=0,"","and c.organ_code = '"+organ+"'")}
)T
;
-- ======================================
-- 在职人数
-- ======================================
SELECT
SUM(zzs) AS SL
FROM
(
SELECT
A1.pq
,SUM(A1.zzs) as zzs
FROM
(
SELECT
pq -- 片区
,ny AS ym
,zzs -- 在职人数
FROM ods_hr_view_mbl_d --
WHERE pq NOT IN ('89','267') -- 这里剔除了佳美物业公司/悦汇发展公司
and ny > '2024-10'
-- 新增历史数据
UNION ALL
SELECT
organ_code as pq
,concat(left(ym,4),'-',right(ym,2)) as ym
,SUM(zzs) AS zzs
-- ,SUM(bzs) AS bzs
FROM ods_hr_history_bz_num
WHERE 1 = 1
GROUP BY organ_code,concat(left(ym,4),'-',right(ym,2))
)A1
WHERE A1.ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
GROUP BY A1.pq
)A
LEFT JOIN
(
SELECT
organ_code
,organ_name
,hr_organ_id
-- ,hr_organ_name
-- ,hr_comm_id
-- ,hr_comm_name
-- ,comm_name
FROM dim_organ_mapping
WHERE length(hr_comm_id) > 0
GROUP BY
organ_code
,organ_name
,hr_organ_id
-- ,hr_organ_name
-- ,hr_comm_id
-- ,hr_comm_name
-- ,comm_name
UNION ALL
SELECT
'0000' AS organ_code -- erp片区编码
,'' AS organ_name -- erp片区名称
,'153' AS hr_organ_id -- hr片区编码
)B
ON A.pq = B.hr_organ_id
where 1 = 1
${IF(LEN(organ)>0," AND B.organ_code = '"+ organ +"'", "")}
-- ======================================
-- 满编率
-- ======================================
-- 整体
${IF(LEN(organ) = 0,"", "/*")}
SELECT
sum(T.full_rate) AS full_rate -- 满编率
,sum(T.full_rate_target) AS full_rate_target -- 满编率目标
,CASE WHEN sum(T.full_rate_target) =0 THEN 0
ELSE sum(T.full_rate) / sum(T.full_rate_target) END AS target_rate -- 目标占比
FROM
(
SELECT
CASE WHEN SUM(bzs) = 0 THEN 0 ELSE SUM(zzs) / SUM(bzs) END AS full_rate -- 满编率
,0 AS full_rate_target -- 满编率目标值
FROM
(
SELECT
SUM(A1.bzs) AS bzs
,SUM(A1.zzs) AS zzs
FROM
(
SELECT
ny
,zzs -- 在职数
,bzs -- 编制数
FROM ods_hr_view_mbl_d
WHERE pq NOT IN ('89', '267') -- 排除了佳美、悦汇
AND ny > '2024-10'
UNION ALL
SELECT
concat(left(ym,4),'-',right(ym,2)) as ny
,SUM(zzs) AS zzs
,SUM(bzs) AS bzs
FROM ods_hr_history_bz_num
WHERE 1 = 1
GROUP BY ym
)A1
WHERE A1.ny = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)A
UNION ALL
SELECT
0 AS full_rate -- 满编率
,full_rate_target -- 满编率目标
FROM ods_hr_collection_target_overall
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)T
${IF(LEN(organ) = 0,"", "*/")}
-- 片区
${IF(LEN(organ) > 0,"", "/*")}
SELECT
CASE WHEN SUM(A.bzs) = 0 THEN 0 ELSE SUM(A.zzs) / SUM(A.bzs) END AS full_rate -- 满编率
,SUM(C.full_rate_target) AS full_rate_target -- 满编率目标值
,CASE WHEN (SUM(A.bzs) = 0 OR coalesce(SUM(C.full_rate_target) ,0) = 0)THEN 0
ELSE (SUM(A.zzs) / SUM(A.bzs)) / SUM(C.full_rate_target) END AS target_rate -- 满编率目标占比
FROM
(
SELECT
A1.ny AS ny
,A1.pq AS pq
,SUM(A1.zzs) AS zzs
,sum(A1.bzs) AS bzs
FROM
(
SELECT
pq -- 片区
,ny
,SUM(zzs) AS zzs -- 在职数
,SUM(bzs) AS bzs -- 编制数
FROM ods_hr_view_mbl_d
WHERE pq not in('89','267') -- 排除了佳美、悦汇
and ny > '2024-10'
GROUP BY pq,ny
UNION ALL
SELECT
organ_code as pq
,concat(left(ym,4),'-',right(ym,2)) as ny
,SUM(zzs) AS zzs
,SUM(bzs) AS bzs
FROM ods_hr_history_bz_num
WHERE 1 = 1
GROUP BY ym,organ_code
)A1
WHERE A1.ny = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
group by A1.ny,A1.pq
)A
LEFT JOIN
(
SELECT
organ_code -- erp片区编码
,organ_name -- erp片区名称
,hr_organ_id -- hr片区编码
,hr_organ_name -- hr片区名称
FROM dim_organ_mapping
WHERE length(hr_organ_id) > 0
GROUP BY
organ_code
,organ_name
,hr_organ_id
,hr_organ_name
UNION ALL
SELECT
'0000' AS organ_code -- erp片区编码
,'' AS organ_name -- erp片区名称
,'153' AS hr_organ_id -- hr片区编码
,'领悦集团总部' AS hr_organ_name -- hr片区名称
)B
ON A.pq = B.hr_organ_id
LEFT JOIN
(
SELECT
organ_code -- erp片区code
,full_rate_target -- 满编率目标
FROM ods_hr_collection_target_organ -- 目标值收集
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)C
ON B.organ_code = C.organ_code
WHERE 1 = 1
${IF(LEN(organ)>0," AND B.organ_code = '"+ organ +"'", "")}
${IF(LEN(organ) > 0,"", "*/")}
-- ======================================
-- 离职率/目标值
-- ======================================
-- 整体
${IF(LEN(organ) = 0,"", "/*")}
SELECT
sum(lz_rate) AS lz_rate
,sum(lz_rate_target) AS lz_rate_target
,CASE WHEN sum(lz_rate_target) = 0 THEN 0
ELSE sum(lz_rate) / sum(lz_rate_target) END AS target_rate
FROM
(
SELECT
CASE WHEN SUM(zzrs) = 0 THEN 0
ELSE SUM(zlzrs) / SUM(zzrs)
END AS lz_rate -- 离职率
,0 AS lz_rate_target -- 离职率目标
FROM
(
SELECT
SUM(A1.zzrs) AS zzrs
,SUM(A1.zlzrs) AS zlzrs
FROM
(
SELECT
LEFT(rq,7) AS ny
,(SUM(CASE WHEN rq = CONCAT(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),'-01') THEN zzrs ELSE 0 END)
+ SUM(CASE WHEN rq = LAST_DAY(CONCAT(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),'-01')) THEN zzrs ELSE 0 END)) / 2
AS zzrs -- 当天在职人数
,SUM(zlzrs) AS zlzrs -- 当天总离职人数
FROM ods_hr_view_lzbl_d
WHERE LEFT(rq,7) >= '2024-10'
AND pq not in('89','267') -- 排除了佳美、悦汇
group by LEFT(rq,7)
UNION ALL
SELECT
T1.ym AS ny
,(SUM(T1.begin_num) + SUM(T1.end_num)) / 2 AS zzrs
,SUM(T1.active_quit) + SUM(T1.pass_quit ) AS zlzrs
FROM
(
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-01' AS ym
,COALESCE(jan_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(jan_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-02' AS ym
,COALESCE(feb_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(feb_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-03' AS ym
,COALESCE(mar_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(mar_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-04' AS ym
,COALESCE(apr_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(apr_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-05' AS ym
,COALESCE(may_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(may_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-06' AS ym
,COALESCE(jun_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(jun_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-07' AS ym
,COALESCE(jul_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(jul_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-08' AS ym
,COALESCE(aug_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(aug_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT
organ_code
,organ_name
,comm_id
,comm_name
,'2024-09' AS ym
,COALESCE(sep_active_quit,0) AS active_quit -- 主动离职人数
,COALESCE(sep_pass_quit,0) AS pass_quit -- 被动离职人数
,COALESCE(sep_begin_num,0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
)T1
GROUP BY T1.ym
)A1
WHERE A1.ny = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)A
UNION ALL
SELECT
0 AS lz_rate
,lz_rate_target -- 离职率目标
FROM ods_hr_collection_target_overall
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)T
${IF(LEN(organ) = 0,"", "*/")}
-- 片区
${IF(LEN(organ) > 0,"", "/*")}
SELECT
CASE WHEN sum(zzrs) = 0 THEN 0
ELSE SUM(zlzrs) / sum(zzrs)
END AS lz_rate
,sum(C.lz_rate_target) AS lz_rate_target
,CASE WHEN (sum(zzrs) = 0 OR sum(lz_rate_target)= 0) THEN 0
ELSE (SUM(zlzrs) / sum(zzrs)) / sum(lz_rate_target)
END AS target_rate
FROM
(
SELECT
A1.pq AS pq
,A1.zzrs AS zzrs
,A1.zlzrs AS zlzrs
FROM
(
SELECT
LEFT(rq, 7) AS ny
, pq -- 片区
,(SUM(CASE WHEN rq = CONCAT( DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),'-01') THEN zzrs ELSE 0 END)
+ SUM(CASE WHEN rq = LAST_DAY(CONCAT(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),'-01')) THEN zzrs ELSE 0 END)) / 2
AS zzrs -- 当天在职人数
, SUM(zlzrs) AS zlzrs -- 当天总离职人数
FROM ods_hr_view_lzbl_d
WHERE LEFT(rq, 7) >= '2024-10'
AND pq NOT IN ('89', '267') -- 排除了佳美、悦汇
-- 排除了:
-- 89:佳美物业公司
-- 153:领悦集团总部
-- 267:悦汇发展公司
GROUP BY pq,LEFT(rq, 7)
UNION ALL
SELECT T1.ym AS ny
, T1.organ_code AS pq
,(SUM(T1.begin_num) + SUM(T1.end_num)) / 2 AS zzrs
, SUM(T1.active_quit) + SUM(T1.pass_quit) AS zlzrs
FROM (SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-01' AS ym
, COALESCE(jan_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(jan_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-02' AS ym
, COALESCE(feb_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(feb_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-03' AS ym
, COALESCE(mar_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(mar_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-04' AS ym
, COALESCE(apr_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(apr_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-05' AS ym
, COALESCE(may_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(may_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-06' AS ym
, COALESCE(jun_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(jun_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-07' AS ym
, COALESCE(jul_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(jul_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-08' AS ym
, COALESCE(aug_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(aug_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date
UNION ALL
SELECT organ_code
, organ_name
, comm_id
, comm_name
, '2024-09' AS ym
, COALESCE(sep_active_quit, 0) AS active_quit -- 主动离职人数
, COALESCE(sep_pass_quit, 0) AS pass_quit -- 被动离职人数
, COALESCE(sep_begin_num, 0) AS begin_num -- 月初在职
,COALESCE(jan_end_num,0) AS end_num -- 月末在职
FROM ods_hr_history_erp_date) T1
GROUP BY T1.ym, T1.organ_code
)A1
WHERE A1.ny = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)A
LEFT JOIN
(
SELECT
organ_code -- erp片区编码
,organ_name -- erp片区名称
,hr_organ_id -- hr片区编码
,hr_organ_name -- hr片区名称
FROM dim_organ_mapping
WHERE length(hr_organ_id) > 0
GROUP BY
organ_code
,organ_name
,hr_organ_id
,hr_organ_name
UNION ALL
SELECT
'0000' AS organ_code -- erp片区编码
,'' AS organ_name -- erp片区名称
,'153' AS hr_organ_id -- hr片区编码
,'领悦集团总部' AS hr_organ_name -- hr片区名称
)B
ON A.pq = B.hr_organ_id
LEFT JOIN
(
SELECT
organ_code
,lz_rate_target
FROM ods_hr_collection_target_organ
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)C
ON B.organ_code = C.organ_code
where 1 = 1
${IF(LEN(organ)>0," AND B.organ_code = '"+ organ +"'", "")}
${IF(LEN(organ) > 0,"", "*/")}
-- ======================================
-- 人力成本费率
-- ======================================
-- 整体
${IF(LEN(organ)=0,"", "/*")}
SELECT
SUM(T.lc_fee_rate) AS lc_fee_rate -- 人力成本费率
,SUM(T.lc_fee_rate_target) AS lc_fee_rate_target -- 人力成本费率目标
FROM
(
SELECT
coalesce(SUM(CASE WHEN cost_type = '人力成本' THEN actual_amount ELSE 0 END),0)
/ (SELECT SUM(index_income_fact) FROM ods_caiwu_feecollection
WHERE category = '物业'
AND yr_month = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m') AND organ_name <> '佳美物业') AS lc_fee_rate -- 人力成本费率
,0 AS lc_fee_rate_target -- 人力成本费率
from dw.ods_cost_collection_lc_ac
WHERE left(ym,4) = left(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),4)
AND ym <= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
UNION ALL
SELECT
0 AS lc_fee_rate
,lc_fee_rate_target -- 人力成本费率
FROM ods_hr_collection_target_overall
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)T
${IF(LEN(organ)=0,"", "*/")}
-- 片区
${IF(LEN(organ)>0,"", "/*")}
SELECT
T.lc_fee_rate AS lc_fee_rate -- 人力成本费率
,T.lc_fee_rate_target AS lc_fee_rate_target -- 人力成本费率目标
FROM
(
SELECT
A.lc_total_fee / B.index_income_fact AS lc_fee_rate
,C.lc_fee_rate_target AS lc_fee_rate_target -- 人力成本费率
FROM
(
-- 费控人力行政成本填报
SELECT
organ_code
,coalesce(SUM(CASE WHEN cost_type = '人力成本' THEN actual_amount ELSE 0 END),0) AS lc_total_fee -- 人力成本费用
from dw.ods_cost_collection_lc_ac
WHERE left(ym,4) = left(DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m'),4)
AND ym <= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
GROUP BY organ_code
)A
LEFT JOIN
(-- 公司实际收入
SELECT
organ_code
,SUM(index_income_fact) AS index_income_fact -- 实际收入
FROM ods_caiwu_feecollection
WHERE category = '物业'
AND yr_month = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
GROUP BY organ_code
)B
ON A.organ_code = B.organ_code
LEFT JOIN
(
SELECT
organ_code
,lc_fee_rate_target -- 人力成本费率目标
,ac_fee_rate_target -- 行政成本费率目标
FROM ods_hr_collection_target_organ
WHERE ym = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')
)C
ON A.organ_code = C.organ_code
WHERE 1 = 1
${IF(LEN(organ)>0," AND A.organ_code = '"+ organ +"'", "")}
)T
${IF(LEN(organ)>0,"", "*/")}
Reference in New Issue Copy Permalink