Query View Reporting Helpdesk

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 m.created_at > d.end_date_assignee 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
	left join mapping_keyword k on (a.id = k.tiket_id)
	left join keyword l on (l.id = k.keyword_id)
	LEFT JOIN (SELECT ticket_id,min(created_at) created_at FROM ticket_status_history
	WHERE type_id = 4
	GROUP BY ticket_id ) m on a.id = m.ticket_id
	WHERE
	-- -- A.created_at <= CURRENT_TIMESTAMP - INTERVAL '120 day ago' -- AND a.id> 79


		d.task_pic = TRUE
		and d.task_pic = true

		and
		j.type_id = 5







		AND LOWER(d.username_assignee) LIKE LOWER('%01-01-2022%')


		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,
		m.created_at
		ORDER BY
		f.name) A




		GROUP BY
		A.layanan