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