chengben/Scripts/成本大屏租赁费中间表.sql

--drop table sqmdb_cost.rpt_3rd_tower_cost
--create table sqmdb_cost.rpt_3rd_tower_cost as 
select 
tgrid.smonth,
a.*,
tgrid.tower_cost_avg,
dc.sort_id as sort_id_city
from
(select 
distinct
s.station_sid,
s.station_sname,
s.city_name,
s.area_name,
s.property_type,
s.station_level,
s.tower_code,
hn.sid as ht_sid,
hn.sname as ht_sname,
hn.yuezujin as ht_yuezujin,
hn.cbzx as ht_cbzx,
t.site_addr_id,
t.total_cost as tower_cost,
dtc.tower_name,
dtc.cbzx as tower_cbzx,
(coalesce(hn.yuezujin,0)+coalesce(t.total_cost,0)) as cost_total,
case 
when hn.sid is not null and t.site_addr_id is not null then '租用铁塔+租用第三方'
when hn.sid is not null and t.site_addr_id is null then '租用第三方'
when hn.sid is null and t.site_addr_id is not null then '租用铁塔'
else '' end as zulin_type,
dgc.grid_code
from sqmdb_cost.station3 s
left join sqmdb_cost.station_hetong sh on s.station_sid=sh.station_sid 
left join sqmdb_cost.hetong_new hn on sh.hetong_sid=hn.sid 
left join 
(select site_addr_id,sum(total_cost) as total_cost from sqmdb_cost.tower3 where sdate='202205' group by site_addr_id) t 
on s.tower_code=t.site_addr_id
left join sqmdb_cost.dict_tower_cbzx dtc on t.site_addr_id=dtc.tower_code 
left join sqmdb_cost.dict_grid_code dgc on city_name_short(s.city_name)=dgc.city_name and trim(s.area_name)=dgc.grid_name 
where s.station_sid>0 and (hn.sid is not null or t.total_cost is not null))a
join 
(select smonth,grid_code,tower_cost_avg from sqmdb_cost.rpt_tower_cost_grid where smonth='202205') tgrid
on a.grid_code=tgrid.grid_code
left join sqmdb_cost.dict_city dc on a.city_name=dc.city_name 
order by dc.sort_id