Paste #177447

with sla_ticket as (select
	tt.id as ticketId,
    t.coordinator_user_uuid,
    round(extract(EPOCH from tt.kd_sla - coalesce(tt.sla_stopped, tt.end_time, '2021-04-28 08:57:20.465'))) as sla_time_remain,
    round(((extract(EPOCH from tt.kd_sla - coalesce(tt.sla_stopped, tt.end_time, '2021-04-28 08:57:20.465'))) / 60) / nullif(tt.original_sla::float, 0) * 100) as sla
    from trouble_tickets tt left join tasks t on tt.id = t.ticket_id
    where tt.ticket_type = 1 
    and t.coordinator_user_uuid is not null  
    and tt.ticket_status in ('1') and t.coordinator_id in (50) 
    group by tt.id, t.coordinator_user_uuid, sla having  count(*) = 1)
        select
            csu.name as coordinator_name,
            tt1.id as ticket_id,
            st.sla as sla_percent_remain,
            st.sla_time_remain,
            tt1.sla_stopped,
            cp.name as priority,
            cc.organization_name as client_name,
            tt1.updated,
            ctt.name as ticket_type,
            cts.name as ticket_subtype
        from
            trouble_tickets tt1
                join sla_ticket st on tt1.id = st.ticketId
                left join cms_client cc on tt1.cms_client_id = cc.id
                left join cat_priority cp on tt1.priority_id = cp.id
                left join cat_ticket_type ctt on tt1.ticket_type_id = ctt.id
                left join cat_ticket_subtype cts on tt1.ticket_subtype_id = cts.id
                left join cache_sec_user csu on st.coordinator_user_uuid = csu.id
        where tt1.start_time < '2021-04-28 08:57:20.465'  
      and st.sla < 1 
      order by coordinator_name desc  offset 0 limit 15;