--11����ҵ���������ɱ�
---����
select 
cost_t.smonth as MONTH_ID,
'����' as ZB_TYPE,
'�ӱ�' as PROV_NAME,
cost_t.city_code*100 as CITY_ID,
cost_t.city_name as CITY_NAME,
null as GRID_ID,
null 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,dg.city_name,dg.city_code,a.cost_yw,a.cost_hexin from
(select '202207' as smonth,sname,
sum(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_yw,
sum(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin
from sqmdb_cost.report50
where period_type='M' 
and sname in('ʯ��ׯ_�ӱ�','��ɽ_�ӱ�','�ػʵ�_�ӱ�','����_�ӱ�','��̨_�ӱ�','����_�ӱ�','�żҿ�_�ӱ�','�е�_�ӱ�','�ȷ�_�ӱ�','����_�ӱ�','��ˮ_�ӱ�','�۰�_�ӱ�')
and smonth between '202201' and '202207' group by sname) a
join (select distinct city_code,city_pro,city_name from sqmdb_cost.dict_grid) dg 
on a.sname=dg.city_pro) cost_t
join
(select dg.city_name,avg_num(y1.yewul,y2.yewul) as yewul from
(select city_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='�ϼ�' and smonth='202112' group by city_name) y1
join
(select city_name,sum(yewul) as yewul from sqmdb_cost.zpzs z where grid_name!='�ϼ�' and smonth='202207' group by city_name) y2
on y1.city_name=y2.city_name
join 
(select distinct city_sname,city_code,city_name from sqmdb_cost.dict_grid) dg
on y1.city_name=dg.city_sname) ywl_t
on cost_t.city_name=ywl_t.city_name
order by cost_t.city_code