chengben/Scripts/kpi31/07_b10.sql

--07、百元净资产网运成本
---北10
select 
'202206' as MONTH_ID,
'北10' as ZB_TYPE,
a.sname as PROV_NAME,
null as CITY_ID,
null as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'7' as ZB_CODE,
'百元净资产网运成本' as ZB_NAME,
'否' as IS_RATIO,
c.cost_total as QZ_FZ,
a.zichan-b.zhejiu as QZ_FM,
c.cost_total/(a.zichan-b.zhejiu)*100 as BY_QZ
from 
(select sname,stype,sum(column7+column8)/2 as zichan 
from sqmdb_cost.report80054 where period_type='M' and smonth between '202201' and '202206' group by sname,stype) a
left join
(select sname,stype,sum(column16) as zhejiu 
from sqmdb_cost.report80053 where period_type='M' and smonth between '202201' and '202206' group by sname,stype) b
on a.sname=b.sname and a.stype=b.stype
left join
(select sname,stype,sum(column1)/10000 as cost_total 
from sqmdb_cost.report50 where period_type='M' and smonth between '202201' and '202206' group by sname,stype) c
on a.sname=c.sname and a.stype=c.stype
where a.sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')