chengben/Scripts/kpi31/09_city.sql

--09、百元资产修理维护费
---地市
select 
t1.smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
dg.city_code*100 as CITY_ID,
t1.sname as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'9' as ZB_CODE,
'百元资产修理维护费' as ZB_NAME,
'否' as IS_RATIO,
t1.cost_fix as QZ_FZ,
t2.zichan as QZ_FM,
(t1.cost_fix/t2.zichan)*100 as BY_QZ
from
(select smonth,stype,sname,cost_fix from sqmdb_cost.chengben_shengnei where smonth='202206' and stype='地市') t1
join
(select sname,sum(column7+column8)/2 as zichan from sqmdb_cost.report80054 where period_type='M'
and sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth between '202201' and '202206' group by sname) t2
on t1.sname=t2.sname
join 
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) dg
on t1.sname=dg.city_pro
order by dg.city_code