Reporting Ringkasan
teguhikhlas
pgsql
2 years ago
5.3 kB
8
Indexable
SELECT CAST(AVG(lama_pengerjaan) AS DECIMAL(8,2)) rata_rata_pengerjaan, COUNT(status_lewat_deadline) AS status_deadline, SUM(CASE WHEN status_lewat_deadline = 'melewati batas' THEN 1 ELSE 0 END) AS melewati_batas, SUM(CASE WHEN status_lewat_deadline = 'tidak melewati batas' THEN 1 ELSE 0 END) AS tidak_melewati_batas, A.layanan AS Layanan FROM (SELECT DISTINCT CAST(A.ID AS VARCHAR) AS No_Tiket, CAST(ARRAY_AGG (l.keyword_name) AS VARCHAR) keyword, -- (case when ( MAX ( j.created_at ) > d.end_date_assignee) then 'melewati batas' (case when extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 5) - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 > e.sla then 'melewati batas' else 'tidak melewati batas' end) as status_lewat_deadline, cast(extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 4) - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 AS decimal(8,2)) lama_pengerjaan, cast(extract(epoch from (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 5) - (select max(created_at) from ticket_status_history sub_a where sub_a.ticket_id = a.id and type_id = 2))/3600 AS decimal(8,2)) lama_ticket_sampai_ditutup, A.username_user AS user_name, A.feedback_user, CAST(A.score AS VARCHAR), regexp_replace( description_user, E'<[^>]+>', '', 'gi' ) AS deskripsi_tiket, f.name AS Layanan, e.NAME AS Sub_Layanan, p.name AS Prioritas, h.NAME AS Tujuan, G.NAME AS klasifikas, b.username_assignor AS Petugas_assign, d.username_assignee AS penerima_tugas, CAST(A.created_at AS VARCHAR) AS Tanggal_Pembuatan, CAST(d.created_at AS VARCHAR) AS Tanggal_Penugasan, CAST(d.end_date_assignee AS VARCHAR) AS Tanggal_Deadline, CAST(d.updated_at AS VARCHAR) AS tanggal_selesai_eksekutor, CAST(MAX ( j.updated_at ) AS VARCHAR) AS tanggal_penutup_tiket, C.task_description AS resolusi, CAST(e.sla AS VARCHAR) sla_layanan FROM ( SELECT DISTINCT username_user, type_id, sub_service_id, score, requester, reporter, rating, is_active, ID, feedback_user, description_user, created_by, created_at FROM tickets ) A LEFT JOIN assignments b ON A.ID = b.ticket_id JOIN priority p ON b.priority_id = p.id JOIN task C ON b.ID = C.assignment_id JOIN assignees d ON C.ID = d.task_id JOIN sub_service e ON A.sub_service_id = e.ID JOIN services f ON e.service_id = f.ID JOIN classifications G ON b.classification_id = G.ID JOIN purpose h ON b.purpose_id = h.ID JOIN types i ON A.type_id = i.ID JOIN ticket_status_history j ON A.ID = j.ticket_id AND j.type_id = A.type_id join mapping_keyword k on (a.id = k.tiket_id) join keyword l on (l.id = k.keyword_id) WHERE -- A.created_at <= CURRENT_TIMESTAMP - INTERVAL '120 day ago' -- AND a.id > 79 AND d.task_pic = TRUE and d.task_pic = true AND h.id = AND G.id = AND f.id = AND e.id = AND j.type_id = AND p.id = AND d.username_assignee = AND TO_CHAR(A.created_at,'YYYY-MM-DD') >= '' AND TO_CHAR(A.created_at,'YYYY-MM-DD') <= '' AND TO_CHAR(j.created_at,'YYYY-MM-DD') >= '' AND TO_CHAR(j.created_at,'YYYY-MM-DD') >= '' GROUP BY A.ID, -- l.keyword_name, A.username_user, A.feedback_user, A.score, A.description_user, f.name, e.NAME, h.NAME, G.NAME, p.name, b.username_assignor, d.username_assignee, A.created_at, d.end_date_assignee, d.updated_at, d.created_at, C.task_description, e.sla ORDER BY f.name) A GROUP BY A.layanan
Editor is loading...