1234567891011121314151617181920212223242526272829303132333435363738394041 |
- 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'
|