Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- case
- when nba."attribute" ->> 'category' in ('mkd_15','mkd_28','mkd_17') then (nba."attribute" #>> '{attribute,UNOM,value}')::int
- when nba."attribute" ->> 'category' in ('yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2') then (ng_odh_dt.ods_object_id)
- else (ng_odh_dt.ods_object_id)
- end as "ID объекта",
- case
- when nba."attribute" ->> 'category' in ('mkd_15','mkd_28','mkd_17') then 'МКД'
- when nba."attribute" ->> 'category' in ('yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2') then 'ДТ'
- when ng_odh_dt.cleaning_category = '9 категория' then 'ТПУ'
- else 'ОДХ'
- end as "Тип объекта",
- nba."attribute" ->> 'category' as "ID нарушения",
- nba."attribute" ->> 'status' as "Статус",
- boo.system_name as "Система",
- (nba."attribute" ->> 'created')::TIMESTAMP as "Дата начала",
- case
- when nba."attribute" ->> 'status' = 'COMPLETE' then (nba."attribute" ->> 'updated')::TIMESTAMP
- else null
- end as "Дата завершения",
- case
- when nba."attribute" ->> 'status' = 'COMPLETE' then (FLOOR(EXTRACT(epoch FROM (nba."attribute" ->> 'updated')::TIMESTAMP - (nba."attribute" ->> 'created')::TIMESTAMP)/3600))::int
- else (FLOOR(EXTRACT(epoch FROM (now())::TIMESTAMP - (nba."attribute" ->> 'created')::TIMESTAMP)/3600))::int
- end AS "Дельта",
- (nba."attribute" ->> 'id') as "NG_Issue_ID"
- from monitor_ba.ng_ba_tmp nba
- left join (
- select *
- from (
- select * from monitor_dict.ng_dt ndt
- union all
- select * from monitor_dict.ng_odh nodh
- )woo
- LEFT OUTER join monitor_dict.egip_odh e_odh on ((woo.ods_object_id)::int = (e_odh.uid_nftn)::int)
- ) ng_odh_dt on (nba."attribute" #>> '{attribute,OBJECT,value}')::int = ng_odh_dt.ng_id,
- (
- select *
- from (values
- ('МКД', 'NG')
- ) as woo (object_type, system_name)) boo
- where nba."attribute" ->> 'category' in (
- 'mkd_15','mkd_28','mkd_17',
- 'yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2',
- 'road_1','road_11','road_13','road_17','road_20','road_4',
- 'stop_4','stop_5','stop_7','stop_8','stop_9',
- 'road_11','road_20','road_13','metro_1',
- 'stop_5','stop_7','stop_9','stop_8','stop_4','stop_2'
- )
- and (nba."attribute" ->> 'created')::TIMESTAMP between '2019-03-17 00:00:00' and '2019-03-17 23:59:59'
- and nba.is_active
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement