select
case
    when nba."attribute" ->> 'category' in ('mkd_15','mkd_28','mkd_17') then (b_attr."UNOM")
    when nba."attribute" ->> 'category' in ('yard_22','yard_35','yard_31','yard_4','yard_3','yard_28','yard_2') then (b_attr."OBJECT")
    else (b_attr."OBJECT")
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 'ДТ'
    else 'ОДХ или ТПУ'
end as "ID объекта",
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 nba
LEFT OUTER join monitor_ba.borisov b_attr on b_attr.ng_issue_id = nba.external_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