chengben/Scripts/kpi31/11_grid.sql

--11、单业务量移网成本
---网格
select 
cost_t.smonth as MONTH_ID,
'网格' as ZB_TYPE,
'河北' as PROV_NAME,
dcg.city_code*100 as CITY_ID,
dcg.city_name as CITY_NAME,
dcg.grid_code as GRID_ID,
dcg.grid_name as GRID_NAME,
'11' as ZB_CODE,
'单业务量移网成本' as ZB_NAME,
'否' as IS_RATIO,
cost_t.cost_yw+cost_t.cost_hexin as QZ_FZ,
ywl_t.yewul as QZ_FM,
(cost_t.cost_yw+cost_t.cost_hexin)/ywl_t.yewul/7 as BY_QZ
from 
(select a.smonth,a.city_name,a.grid_name,dgc.grid_code,a.cost_yw,a.cost_hexin from
(select smonth,city_name_short(city_name) as city_name,grid_name,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_yw,
(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin
from sqmdb_cost.report49
where period_type='Y' 
and smonth='202207') a 
join sqmdb_cost.dict_grid_code dgc 
on a.city_name=dgc.city_name and trim(a.grid_name)=trim(dgc.grid_name)) cost_t
join
(select dg.city_name,dg.grid_code,dg.grid_name,avg_num(y1.yewul,y2.yewul) as yewul from
(select city_name,grid_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202112' group by city_name,grid_name) y1
join
(select city_name,grid_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='合计' and smonth='202207' group by city_name,grid_name) y2
on y1.city_name=y2.city_name and trim(y1.grid_name)=trim(y2.grid_name)
join sqmdb_cost.dict_grid_code dg
on y1.city_name=dg.city_name and trim(y1.grid_name)=trim(dg.grid_name) 
where dg.grid_code is not null and avg_num(y1.yewul,y2.yewul)>0) ywl_t
on cost_t.grid_code=ywl_t.grid_code
join sqmdb_cost.dict_code_grid dcg on cost_t.grid_code=dcg.grid_code 
order by dcg.city_code