SHARE
TWEET

Untitled

a guest Mar 22nd, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2. case
  3.     when nba."attribute" ->> 'category' in ('mkd_15','mkd_28','mkd_17') then (nba."attribute" #>> '{attribute,UNOM,value}')::int
  4.     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)
  5.     else (ng_odh_dt.ods_object_id)
  6. end as "ID объекта",
  7. case
  8.     when nba."attribute" ->> 'category' in ('mkd_15','mkd_28','mkd_17') then 'МКД'
  9.     when nba."attribute" ->> 'category' in ('yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2') then 'ДТ'
  10.     when ng_odh_dt.cleaning_category = '9 категория' then 'ТПУ'
  11.     else 'ОДХ'
  12. end as "Тип объекта",
  13. nba."attribute" ->> 'category' as "ID нарушения",
  14. nba."attribute" ->> 'status' as "Статус",
  15. boo.system_name as "Система",
  16. (nba."attribute" ->> 'created')::TIMESTAMP as "Дата начала",
  17. case
  18.     when nba."attribute" ->> 'status' = 'COMPLETE' then (nba."attribute" ->> 'updated')::TIMESTAMP
  19.     else null
  20. end as "Дата завершения",
  21. case
  22.     when nba."attribute" ->> 'status' = 'COMPLETE' then (FLOOR(EXTRACT(epoch FROM (nba."attribute" ->> 'updated')::TIMESTAMP - (nba."attribute" ->> 'created')::TIMESTAMP)/3600))::int
  23.     else (FLOOR(EXTRACT(epoch FROM (now())::TIMESTAMP - (nba."attribute" ->> 'created')::TIMESTAMP)/3600))::int
  24. end AS "Дельта",
  25. (nba."attribute" ->> 'id') as "NG_Issue_ID"
  26. from monitor_ba.ng_ba_tmp nba
  27. left join (
  28. select *
  29. from (
  30. select * from  monitor_dict.ng_dt ndt
  31. union all
  32. select * from monitor_dict.ng_odh nodh
  33. )woo
  34. LEFT OUTER join monitor_dict.egip_odh e_odh on ((woo.ods_object_id)::int = (e_odh.uid_nftn)::int)
  35. ) ng_odh_dt on (nba."attribute" #>> '{attribute,OBJECT,value}')::int = ng_odh_dt.ng_id,
  36. (
  37. select *
  38. from (values
  39. ('МКД', 'NG')
  40. ) as woo (object_type, system_name)) boo
  41. where nba."attribute" ->> 'category' in (
  42. 'mkd_15','mkd_28','mkd_17',
  43. 'yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2',
  44. 'road_1','road_11','road_13','road_17','road_20','road_4',
  45. 'stop_4','stop_5','stop_7','stop_8','stop_9',
  46. 'road_11','road_20','road_13','metro_1',
  47. 'stop_5','stop_7','stop_9','stop_8','stop_4','stop_2'
  48. )
  49. and (nba."attribute" ->> 'created')::TIMESTAMP between '2019-03-17 00:00:00' and '2019-03-17 23:59:59'
  50. and nba.is_active
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top