SELECT
t1.repair_sheet_no -- '維修單號(hào)',
,t1.repair_mode_name --'維修方式名稱',
,t1.factory_id
,t1.factory_name -- '維修廠名稱',
,t1.factory_aptitude_id --'維修廠資質(zhì)id',
,t1.factory_aptitude_name -- '維修廠資質(zhì)名稱',
,t1.factory_city_id --'維修廠城市id',
,t1.factory_city_name --'維修廠城市name',
,t1.bill_create_time -- '工單新建時(shí)間',
,t1.end_time -- 工單結(jié)束時(shí)間',
,t1.apply_dept_name -- '工單申請(qǐng)門店',
,t1.apply_city_name -- '工單申請(qǐng)城市',
,t1.first_propose_emp_name -- '首次提報(bào)方案操作人名稱',
,t1.first_propose_emp_dept_name --'首次提報(bào)方案操作人部門名稱',
,t1.create_emp_name ---工單新建人
,t1.repair_sheet_total_amt --'維修總金額',
,t2.relate_self_factory_name_list --'關(guān)聯(lián)自建廠名稱'
,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 --'工單時(shí)間（天）',
,t1.is_big_trouble --'是否大事故',
,t1.big_trouble_days --'理論大事故時(shí)長',
,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 --'是否大事故超時(shí)',
,t1.new_repair_project_type_name --'新的工單類型',
FROM
(SELECT
repair_sheet_no -- '維修單號(hào)',
,repair_mode_name --'維修方式名稱',
,factory_id
,factory_name -- '維修廠名稱',
,CASE WHEN factory_name LIKE '%神州%' then factory_name
WHEN factory_name LIKE '%保養(yǎng)點(diǎn)%' then factory_name else first_propose_emp_dept_name end perfomens_factory --績效維修廠
,factory_aptitude_id --'維修廠資質(zhì)id',
,factory_aptitude_name -- '維修廠資質(zhì)名稱',
,factory_city_id --'維修廠城市id',
,factory_city_name --'維修廠城市name',
,repair_project_type_name --'維修工單類型',
,bill_create_time -- '工單新建時(shí)間',
,apply_dept_name -- '工單申請(qǐng)門店',
,apply_city_name -- '工單申請(qǐng)城市',
,end_time -- 工單結(jié)束時(shí)間',
,first_propose_emp_name -- '首次提報(bào)方案操作人名稱',
,first_propose_emp_dept_name --'首次提報(bào)方案操作人部門名稱',
,create_emp_name --工單新建人
,case when repair_mode_name='監(jiān)控設(shè)備維護(hù)' 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='進(jìn)廠維修' and (first_propose_emp_name =create_emp_name) then first_propose_emp_name
when repair_mode_name='進(jìn)廠維修' and first_propose_emp_name is null then create_emp_name
when repair_mode_name='進(jìn)廠維修' 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 --'工單時(shí)間（天）',
,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 --'理論大事故時(shí)長(天）',
,case when repair_sheet_total_amt >'10000' then '1' else '0' end is_big_trouble --'是否大事故',
,case when repair_mode_name='監(jiān)控設(shè)備維護(hù)' and repair_project_type_name is null then '保養(yǎng)及易損件更換'
when repair_mode_name ='進(jìn)廠維修' and repair_project_type_name in ('保養(yǎng)類','寶沃保養(yǎng)類','寶沃易損件','美容類','易損件維修類') then '保養(yǎng)及易損件更換'
when (repair_mode_name='進(jìn)廠維修' and repair_project_type_name is null) or repair_project_type_name in ('故障維修類','寶沃故障類') then '故障維修'
when repair_mode_name='上門維保' and repair_project_type_name in ('保養(yǎng)類','寶沃易損件','易損件維修類','寶沃保養(yǎng)類') then '工人做單，不給前臺(tái)算單量'
when repair_mode_name in('上門維保','進(jìn)廠維修','只定不修') and repair_project_type_name in ('事故類','寶沃事故類','寶沃外觀類','外觀維修類' ) then '事故及大修'
when repair_mode_name in('上門維保','進(jìn)廠維修','只定不修') 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<>'補(bǔ)單'

) t1

left join

(
SELECT
dim.id -- '維修廠ID',
,dim.name --'維修廠名稱',
,dim.aptitude_name -- '資質(zhì)名稱',
,dim.city_id --'城市ID',
,dim.city_name --'城市名稱',
,case when dim.name='拉薩保養(yǎng)點(diǎn)' then '神州西安維修廠（凱普）'
when dim.name='洛陽保養(yǎng)點(diǎn)' then '神州鄭州維修廠（眾德立）'
when dim.name='麗江保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.name='西雙版納保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.name='大理保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.relate_self_factory_name_list ='大理保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.relate_self_factory_name_list ='西雙版納保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.relate_self_factory_name_list ='麗江保養(yǎng)點(diǎn)' then '神州昆明維修廠（萬眾）'
when dim.relate_self_factory_name_list ='洛陽保養(yǎng)點(diǎn)' then '神州鄭州維修廠（眾德立）'
when dim.relate_self_factory_name_list ='拉薩保養(yǎng)點(diǎn)' then '神州西安維修廠（凱普）'
else dim.relate_self_factory_name_list end relate_self_factory_name_list
,case when dim.name='拉薩保養(yǎng)點(diǎn)' then '西安'
when dim.name='洛陽保養(yǎng)點(diǎn)' then '鄭州'
when dim.name='麗江保養(yǎng)點(diǎn)' then '昆明'
when dim.name='西雙版納保養(yǎng)點(diǎn)' then '昆明'
when dim.name='大理保養(yǎng)點(diǎn)' 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 -- '資質(zhì)名稱',
,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 -- '關(guān)聯(lián)自有維修廠名稱(合作廠必填)',
,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 -- '關(guān)聯(lián)自有維修廠城市名稱(合作廠必填)',
,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'%備管理點(diǎn)%' and name not like'%虛擬廠%' and name not like'%寶沃新零售%'
and name not like'%瑞幸T3%' and name not like'%總部資產(chǎn)監(jiān)控設(shè)備組%' and name not like'%寶沃中國授權(quán)商待分配%' and name not like'%資產(chǎn)%'
-- and status = 1
) dim
) t2

on t1.factory_id=t2.id

鏈接：https://www.dianjilingqu.com/613848.html