123456789101112131415161718192021222324252627282930313233343536373839404142434445 |
- --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
|