chengben/Scripts/合同更新.sql

--truncate table sqmdb_cost.hetong_new 
--insert into sqmdb_cost.hetong_new 
select 
ht1.*,
case when ht1.nmonth=0 then 0 else ht1.cost_total/ht1.nmonth end as yuezujin,
ht2.cost_center_name 
from
(select 
*,
finance_rmbmoney-finance_rmb_vatax as cost_total,--人民币金额-人民币增值税（原始“人民币不含增值税总金额”字段金额不全，有大部分0）
extract(year from age(end_date::timestamp+'1 day',start_date::timestamp))*12+
extract(month from age(end_date::timestamp+'1 day',start_date::timestamp))+
extract(day from age(end_date::timestamp+'1 day',start_date::timestamp))/30 as nmonth
from sqmdb_cost.hetong_jibenxinxi_e
where vision_flag='当前版本'
and arrair_status_name in ('履行中','审签中')
and contract_status_name in ('履行中','审签中')
and contract_cate_l1_code=20000
and contract_cate_l3_name in ('支出类-网络运行及支撑成本-租赁','支出类-工程建设类-基站/设备用房租赁')
and end_date::timestamp>date_trunc('month',now())) ht1
left join
(select period_id,contract_no_global,cost_center_name from
(select period_id,contract_no_global,cost_center_name,
row_number() over(partition by contract_no_global order by period_id::numeric desc) as rn
from sqmdb_cost.hetong_yuti_e
where contract_no_global is not null
order by contract_no_global desc) cbzx
where rn=1) ht2
on ht1.contract_no_global=ht2.contract_no_global


--合同无结束日期
select 
*,
extract(year from age(end_date::timestamp+'1 day',start_date::timestamp))*12+
extract(month from age(end_date::timestamp+'1 day',start_date::timestamp))+
extract(day from age(end_date::timestamp+'1 day',start_date::timestamp))/30 as nmonth
from sqmdb_cost.hetong_jibenxinxi_e
where vision_flag='当前版本'
and arrair_status_name in ('履行中','审签中')
and contract_status_name in ('履行中','审签中')
and contract_cate_l1_code=20000
and contract_cate_l3_name in ('支出类-网络运行及支撑成本-租赁','支出类-工程建设类-基站/设备用房租赁')
and end_date is null