维修效率时长

SELECT
t1.repair_sheet_no -- '维修单号',
,t1.repair_mode_name --'维修方式名称',
,t1.factory_id
,t1.factory_name -- '维修厂名称',
,t1.factory_aptitude_id --'维修厂资质id',
,t1.factory_aptitude_name -- '维修厂资质名称',
,t1.factory_city_id --'维修厂城市id',
,t1.factory_city_name --'维修厂城市name',
,t1.bill_create_time -- '工单新建时间',
,t1.end_time -- 工单结束时间',
,t1.apply_dept_name -- '工单申请门店',
,t1.apply_city_name -- '工单申请城市',
,t1.first_propose_emp_name -- '首次提报方案操作人名称',
,t1.first_propose_emp_dept_name --'首次提报方案操作人部门名称',
,t1.create_emp_name ---工单新建人
,t1.repair_sheet_total_amt --'维修总金额',
,t2.relate_self_factory_name_list --'关联自建厂名称'
,case when t1.perfomens_factory not like '%神州%' then t2.relate_self_factory_name_list else t1.perfomens_factory end perfomens_factory
,t1.perfomens_name ---绩效人
,t1.days --'工单时间（天）',
,t1.is_big_trouble --'是否大事故',
,t1.big_trouble_days --'理论大事故时长',
,case when t1.is_big_trouble=1 and t1.days >= t1.big_trouble_days then 1
when t1.is_big_trouble=0 then 0 else 0 end is_big_trouble_delay --'是否大事故超时',
,t1.new_repair_project_type_name --'新的工单类型',
FROM
(SELECT
repair_sheet_no -- '维修单号',
,repair_mode_name --'维修方式名称',
,factory_id
,factory_name -- '维修厂名称',
,CASE WHEN factory_name LIKE '%神州%' then factory_name
WHEN factory_name LIKE '%保养点%' then factory_name else first_propose_emp_dept_name end perfomens_factory --绩效维修厂
,factory_aptitude_id --'维修厂资质id',
,factory_aptitude_name -- '维修厂资质名称',
,factory_city_id --'维修厂城市id',
,factory_city_name --'维修厂城市name',
,repair_project_type_name --'维修工单类型',
,bill_create_time -- '工单新建时间',
,apply_dept_name -- '工单申请门店',
,apply_city_name -- '工单申请城市',
,end_time -- 工单结束时间',
,first_propose_emp_name -- '首次提报方案操作人名称',
,first_propose_emp_dept_name --'首次提报方案操作人部门名称',
,create_emp_name --工单新建人
,case when repair_mode_name='监控设备维护' then create_emp_name
when repair_mode_name='上门维保' then first_propose_emp_name
when repair_mode_name='只定不修' then first_propose_emp_name
when repair_mode_name='进厂维修' and (first_propose_emp_name =create_emp_name) then first_propose_emp_name
when repair_mode_name='进厂维修' and first_propose_emp_name is null then create_emp_name
when repair_mode_name='进厂维修' and (first_propose_emp_name <>create_emp_name) and first_propose_emp_name is not null then first_propose_emp_name ELSE first_propose_emp_name end perfomens_name
,repair_sheet_total_amt --'维修总金额',
,datediff(end_time,bill_create_time) days --'工单时间（天）',
,case when repair_sheet_total_amt > '30000' then '40'
when repair_sheet_total_amt > '20000' then '30'
when repair_sheet_total_amt > '10000' then '20' else '0' END big_trouble_days --'理论大事故时长(天）',
,case when repair_sheet_total_amt >'10000' then '1' else '0' end is_big_trouble --'是否大事故',
,case when repair_mode_name='监控设备维护' and repair_project_type_name is null then '保养及易损件更换'
when repair_mode_name ='进厂维修' and repair_project_type_name in ('保养类','宝沃保养类','宝沃易损件','美容类','易损件维修类') then '保养及易损件更换'
when (repair_mode_name='进厂维修' and repair_project_type_name is null) or repair_project_type_name in ('故障维修类','宝沃故障类') then '故障维修'
when repair_mode_name='上门维保' and repair_project_type_name in ('保养类','宝沃易损件','易损件维修类','宝沃保养类') then '工人做单，不给前台算单量'
when repair_mode_name in('上门维保','进厂维修','只定不修') and repair_project_type_name in ('事故类','宝沃事故类','宝沃外观类','外观维修类' ) then '事故及大修'
when repair_mode_name in('上门维保','进厂维修','只定不修') and repair_project_type_name is null then '事故及大修' else '其他' end as new_repair_project_type_name
FROM
ads.ads_vehicle_factory_repair_time_detail_z WHERE etl_date >='2022-11-04' and etl_date<='2022-11-10'
and repair_mode_name<>'补单'

) t1

left join

(
SELECT
dim.id -- '维修厂ID',
,dim.name --'维修厂名称',
,dim.aptitude_name -- '资质名称',
,dim.city_id --'城市ID',
,dim.city_name --'城市名称',
,case when dim.name='拉萨保养点' then '神州西安维修厂（凯普）'
when dim.name='洛阳保养点' then '神州郑州维修厂（众德立）'
when dim.name='丽江保养点' then '神州昆明维修厂（万众）'
when dim.name='西双版纳保养点' then '神州昆明维修厂（万众）'
when dim.name='大理保养点' then '神州昆明维修厂（万众）'
when dim.relate_self_factory_name_list ='大理保养点' then '神州昆明维修厂（万众）'
when dim.relate_self_factory_name_list ='西双版纳保养点' then '神州昆明维修厂（万众）'
when dim.relate_self_factory_name_list ='丽江保养点' then '神州昆明维修厂（万众）'
when dim.relate_self_factory_name_list ='洛阳保养点' then '神州郑州维修厂（众德立）'
when dim.relate_self_factory_name_list ='拉萨保养点' then '神州西安维修厂（凯普）'
else dim.relate_self_factory_name_list end relate_self_factory_name_list
,case when dim.name='拉萨保养点' then '西安'
when dim.name='洛阳保养点' then '郑州'
when dim.name='丽江保养点' then '昆明'
when dim.name='西双版纳保养点' then '昆明'
when dim.name='大理保养点' then '昆明'
when dim.relate_self_factory_city_name_list='大理白族自治州' then '昆明'
when dim.relate_self_factory_city_name_list='丽江' then '昆明' else dim.relate_self_factory_city_name_list end relate_self_factory_city_name_list
from
(SELECT
id -- '维修厂ID',
,name --'维修厂名称',
,aptitude_name -- '资质名称',
,city_id --'城市ID',
,city_name --'城市名称',
,case when relate_self_factory_name_list =''AND aptitude_name ='自建厂' then name else relate_self_factory_name_list end relate_self_factory_name_list -- '关联自有维修厂名称(合作厂必填)',
,case when relate_self_factory_city_name_list ='' AND aptitude_name ='自建厂' then city_name else relate_self_factory_city_name_list end relate_self_factory_city_name_list -- '关联自有维修厂城市名称(合作厂必填)',
,status
FROM
dim.dim_repair_factory WHERE etl_date = date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1)
and name not like'%买买车%' and aptitude_name<>'买买车' and name not like'%备管理点%' and name not like'%虚拟厂%' and name not like'%宝沃新零售%'
and name not like'%瑞幸T3%' and name not like'%总部资产监控设备组%' and name not like'%宝沃中国授权商待分配%' and name not like'%资产%'
-- and status = 1
) dim
) t2

on t1.factory_id=t2.id