chengben/Scripts/IDC报表.sql

select * from
(select smonth,sname,idc_in_total,idc_in_jtft,idc_in_qzc,(idc_in_total-idc_in_jtft-idc_in_qzc) as chazhi 
from sqmdb_cost.chengben_shengnei where smonth='202203' and stype='地市') byue
join 
(select smonth,sname,idc_in_total,idc_in_jtft,idc_in_qzc,(idc_in_total-idc_in_jtft-idc_in_qzc) as chazhi 
from sqmdb_cost.chengben_shengnei where smonth='202202' and stype='地市') syue
on byue.sname=syue.sname

--create table sqmdb_cost.rpt_tmp as 
select smonth,sname,'IDC业务收入' as vtype,idc_in_total as amount from sqmdb_cost.chengben_shengnei where smonth='202203' and stype='地市'
union all 
select smonth,sname,'集团分摊收入' as vtype,idc_in_jtft as amount from sqmdb_cost.chengben_shengnei where smonth='202203' and stype='地市'
union all 
select smonth,sname,'轻资产运营收入' as vtype,idc_in_qzc as amount from sqmdb_cost.chengben_shengnei where smonth='202203' and stype='地市'
union all
select smonth,sname,'减去分摊及轻资产收入' as vtype,(idc_in_total-idc_in_jtft-idc_in_qzc) as amount from sqmdb_cost.chengben_shengnei where smonth='202203' and stype='地市'


select sname,
sum(column4+column5)*0.1512/10000 as id_19,
sum(column4+column5)*0.8488/10000 as id_20,
sum(column19)/10000 as id_21,
sum(column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)/10000 as id_23,
sum(column108)/10000 as id_24,
sum(column5)/10000 as id_25_p2
from sqmdb_cost.report50 r 
where period_type='M' and smonth between '202201' and '202207' and stype='分省展现' and sname ='河北' group by sname
union all
select sname,
(column4+column5)*0.1512/10000 as id_19,
(column4+column5)*0.8488/10000 as id_20,
(column19)/10000 as id_21,
(column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)/10000 as id_23,
(column108)/10000 as id_24,
(column5)/10000 as id_25_p2
from sqmdb_cost.report50 r 
where period_type='Y' and smonth='202207' and stype='分省展现' and sname ='河北'


--网运成本专业分解表-归集+横向分摊+调差_年_分省展现_01合并
select * from sqmdb_cost.report50 where sname='河北' and period_type='Y' and smonth='202207'