chengben/Scripts/大屏_比自己.sql

--预算进度
--chengben_self_1_b10_202203.csv
--03、网运成本预算完成进度（集团口径）
---北10
select smonth,sname,
column1 as total_cost,
column2 as total_ys,
case when column2=0 then 1 else column1/column2 end as rate_ys,
case 
when sname='北京' then 110000
when sname='天津' then 120000
when sname='河北' then 130000
when sname='山西' then 140000
when sname='内蒙古' then 150000
when sname='辽宁' then 210000
when sname='吉林' then 220000
when sname='黑龙江' then 230000
when sname='山东' then 370000
when sname='河南' then 410000
end as prov_code
from sqmdb_cost.report50002 
where sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and smonth='202203'
order by case
when sname='北京' then 1
when sname='天津' then 2
when sname='河北' then 3
when sname='山西' then 4
when sname='内蒙古' then 5
when sname='辽宁' then 6
when sname='吉林' then 7
when sname='黑龙江' then 8
when sname='山东' then 9
when sname='河南' then 10
end


--主要成本（万元）
--chengben_self_2_city_202203.csv
---地市
select 
jt.*,
sn.cost_total_sn,
t2.city_code*100 as city_code 
from
(select 
a.smonth,
a.sname,
a.cost_total_jt,
a.cost_rental_tower_jt,
a.cost_nenghao_jt,
a.cost_rental_3rd_jt
from
(select smonth,sname,stype,
column1/10000 as cost_total_jt,
(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115+column4)/10000 as cost_nenghao_jt,
(column12+column15+column16)/10000 as cost_rental_3rd_jt,
(column2+column3)/10000 as cost_rental_tower_jt
from sqmdb_cost.report50 where stype ='北方省汇总展现') a
left join
(select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b
on a.smonth=b.smonth and a.sname=b.sname
where 
a.sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')) jt
left join
(select smonth,sname,cost_total as cost_total_sn from sqmdb_cost.chengben_shengnei where stype='地市') sn
on jt.smonth=sn.smonth and jt.sname=sn.sname 
left join
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) t2
on jt.sname=t2.city_pro
where jt.smonth='202203'
order by t2.city_code


---网格（无分母）