SELECT distinct
case
    when cba."attribute" #>> '{type,objectType}' = 'YARD_CONTAINER' then (o_mso.dt_id)::int
    else (cba."attribute" #>> '{object,externalId}')::int
end as "ID объекта",
case
    when cba."attribute" #>> '{type,objectType}' = 'ODH' and e_odh.cleaning_category != '9 категория' then 'ОДХ'
    when cba."attribute" #>> '{type,objectType}' = 'YARD' then 'ДТ'
    when cba."attribute" #>> '{type,objectType}' = 'ODH' and e_odh.cleaning_category = '9 категория' then 'ТПУ'
    when cba."attribute" #>> '{type,objectType}' = 'YARD_CONTAINER' then 'ДТ'
end as "Тип объекта",
cba."attribute" #>> '{type,id}' as "ID нарушения",
cba."attribute" #>> '{workflow,status}' as "Статус",
cba."attribute" ->> 'system' as "Система",
(cba."attribute" ->> 'date')::timestamp as "Дата начала",
case
    when '"COMPLETED"' = any(woo."Status_array") then (cba."attribute" ->> 'updated')::timestamp
    else null
end as "Дата завершения",
e_odh.cleaning_category as "Категория уборки",
(FLOOR(EXTRACT(epoch FROM (cba.attribute ->> 'updated')::TIMESTAMP - (cba.attribute ->> 'date')::TIMESTAMP)/3600))::int AS "Дельта",
array_agg(cba.external_id) as "CAFAP_Issue_ID"
FROM monitor_ba.cafap_ba cba
LEFT OUTER JOIN monitor_dict.egip_odh e_odh ON ((cba."attribute" #>> '{object,externalId}')::int = e_odh.uid_nftn)
LEFT OUTER join monitor_dict.ods_mso o_mso on ((cba."attribute" #>> '{object,externalId}')::int = (o_mso.mso_id)::int),
(
select distinct external_id as "CAFAP_Issue_ID",
array_agg(elem -> 'to') as "Status_array"
FROM monitor_ba.cafap_ba cba,
jsonb_each(cba.attribute) as workflow,
jsonb_each(workflow.value) as w_history,
jsonb_array_elements(w_history.value) as elem
where workflow.key = 'workflow'
and w_history.key = 'history'
and (cba."attribute" ->> 'date')::timestamp between '2019-03-12 00:00:00' and '2019-03-12 23:59:59'
group by "CAFAP_Issue_ID"
) woo
where cba.external_id = woo."CAFAP_Issue_ID"
group by "ID объекта", "Тип объекта", "ID нарушения", "Статус", "Система", "Дата начала", "Дата завершения", "Категория уборки",  "Дельта"