chengben/Scripts/19.sql

--19、无线成本占收比
---北10
select 
a.smonth as 月份,
a.sname as 公司,
--a.stype as 数据类型,
a.cost_wireless as 无线网成本,
b.income_year as 本年收入累计,
case when b.income_year=0 then null else a.cost_wireless/b.income_year end as 无线成本占收比
from
(select '202203' as smonth,sname,stype,
sum(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17)/10000 as cost_wireless --column18 账面人工成本
from sqmdb_cost.report50 where smonth between '202201' and '202203' group by sname,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('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and a.smonth='202203'


---地市
select 
a.smonth as 月份,
a.sname as 公司,
--a.stype as 数据类型,
a.cost_wireless as 无线网成本,
b.income_year as 本年收入累计,
case when b.income_year=0 then null else a.cost_wireless/b.income_year end as 无线成本占收比
from
(select '202203' as smonth,sname,
sum(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17)/10000 as cost_wireless --column18 账面人工成本
from sqmdb_cost.report50 where stype ='北方省汇总展现' and smonth between '202201' and '202203' group by sname) 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 ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and a.smonth='202203'