View Reporting Sub Layanan Sql

avatar
teguhikhlas
pgsql
2 years ago
5.9 kB
8
Indexable
SELECT service , sub_service , sla, count(1) as jumlah_keluhan,
round(avg(spend_hours)) as rata_rata_jam,
round(((cast(count(1) filter (where not is_late) as decimal ))/cast(count(1) as decimal)*100),2) as close_sesuai_target,
round(((cast(count(1) filter (where is_late) as decimal ))/cast(count(1) as decimal)*100),2) as
close_tidak_sesuai_target
FROM (
SELECT A
.service,
A.sub_service,
A.ticket_created,
A.closed_time,
A.sla,
A.is_late,
case when
lama_seluruh_hari - floor(jumlah_libur) is null then lama_seluruh_hari - tanggal
else (lama_seluruh_hari - floor(jumlah_libur)) - tanggal end spend_hourS
FROM
(
SELECT
service,
sub_service,
sla,
ticket_created,
is_late,
closed_time,
15.5 * floor(COUNT ( tanggal )-1) tanggal,
( EXTRACT ( EPOCH FROM closed_time - ticket_created ) / 3600 ) lama_seluruh_hari
FROM
(
SELECT
service,
sub_service,
sla,
day_name,
is_late,
ticket_created,
closed_time,
DATE ( generate_series ( DATE ( ticket_created ), DATE ( closed_time ), 'P1D' ) ) AS tanggal
FROM
(
SELECT
s.NAME AS service,
ss.NAME AS sub_service,
ss.sla,
T.created_At,
TRIM ( to_char( T.created_at, 'day' ) ) AS day_name,
tsh.closed_time,
a2.deadline,
( tsh.closed_time :: DATE > a2.deadline :: DATE ) AS is_late,
CASE

WHEN T.created_At :: TIME < '08:00:00' THEN ( concat ( DATE ( T.created_at ), ' 08:00:00' ) :: TIMESTAMP ) WHEN
	T.created_At :: TIME> '16:30:00' THEN
	( concat ( DATE ( T.created_at ) + 1, ' 08:00:00' ) :: TIMESTAMP )
	WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'friday'
	AND T.created_At :: TIME > '17:00:00' THEN
	( concat ( DATE ( T.created_at ) + 3, ' 08:00:00' ) :: TIMESTAMP )
	WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'saturday' THEN
	( concat ( DATE ( T.created_at ) + 2, ' 08:00:00' ) :: TIMESTAMP )
	WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'sunday' THEN
	( concat ( DATE ( T.created_at ) + 1, ' 08:00:00' ) :: TIMESTAMP ) ELSE T.created_at
	END AS ticket_created
	FROM
	tickets
	T INNER JOIN assignments A ON A.ticket_id = T.
	ID INNER JOIN sub_service ss ON A.subservice_id = ss.
	ID INNER JOIN services s ON ss.service_id = s.
	ID JOIN (
	SELECT MAX
	( tsh.created_at ) AS closed_time,
	tsh.ticket_id
	FROM
	ticket_status_history tsh
	WHERE
	tsh.type_id = 5
	GROUP BY
	tsh.ticket_id
	) tsh ON tsh.ticket_id = T.
	ID LEFT JOIN (
	SELECT MAX
	( a2.end_date_assignee ) AS deadline,
	t2.assignment_id
	FROM
	assignees a2
	JOIN task t2 ON ( a2.task_id = t2.ID )
	GROUP BY
	t2.assignment_id
	) a2 ON a2.assignment_id = A.ID
	where T.created_at BETWEEN '2022-01-01 16:46:21' AND '2022-12-31 16:46:21' and (LOWER(s.name) LIKE
	LOWER('%keuangan%') or LOWER(ss.name) LIKE LOWER('%keuangan%'))

	GROUP BY
	s.ID,
	ss.ID,
	T.ID,
	tsh.closed_time,
	a2.deadline,
	T.created_at
	) A
	) b
	WHERE
	tanggal IN ( SELECT work_date FROM calender_work_day WHERE is_work_day = 1 )
	-- AND ticket_created = '2022-01-01'

	GROUP BY
	service,
	sub_service,
	sla,
	is_late,
	ticket_created,
	closed_time
	ORDER BY
	4 ASC
	)
	A LEFT JOIN (
	SELECT
	service,
	sub_service,
	sla,
	ticket_created,
	closed_time,
	COUNT ( tanggal ) * 24 jumlah_libur
	FROM
	(
	SELECT
	service,
	sub_service,
	sla,
	day_name,
	ticket_created,
	closed_time,
	DATE ( generate_series ( DATE ( ticket_created ), DATE ( closed_time ), 'P1D' ) ) AS tanggal
	FROM
	(
	SELECT
	s.NAME AS service,
	ss.NAME AS sub_service,
	ss.sla,
	T.created_At,
	TRIM ( to_char( T.created_at, 'day' ) ) AS day_name,
	tsh.closed_time,
	a2.deadline,
	( tsh.closed_time :: DATE > a2.deadline :: DATE ) AS is_late,
	CASE

	WHEN T.created_At :: TIME < '08:00:00' THEN ( concat ( DATE ( T.created_at ), ' 08:00:00' ) :: TIMESTAMP ) WHEN
		T.created_At :: TIME> '16:30:00' THEN
		( concat ( DATE ( T.created_at ) + 1, ' 08:00:00' ) :: TIMESTAMP )
		WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'friday'
		AND T.created_At :: TIME > '17:00:00' THEN
		( concat ( DATE ( T.created_at ) + 3, ' 08:00:00' ) :: TIMESTAMP )
		WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'saturday' THEN
		( concat ( DATE ( T.created_at ) + 2, ' 08:00:00' ) :: TIMESTAMP )
		WHEN TRIM ( to_char( T.created_at, 'day' ) ) = 'sunday' THEN
		( concat ( DATE ( T.created_at ) + 1, ' 08:00:00' ) :: TIMESTAMP ) ELSE T.created_at
		END AS ticket_created
		FROM
		tickets
		T INNER JOIN assignments A ON A.ticket_id = T.
		ID INNER JOIN sub_service ss ON A.subservice_id = ss.
		ID INNER JOIN services s ON ss.service_id = s.
		ID JOIN (
		SELECT MAX
		( tsh.created_at ) AS closed_time,
		tsh.ticket_id
		FROM
		ticket_status_history tsh
		WHERE
		tsh.type_id = 5
		GROUP BY
		tsh.ticket_id
		) tsh ON tsh.ticket_id = T.
		ID LEFT JOIN (
		SELECT MAX
		( a2.end_date_assignee ) AS deadline,
		t2.assignment_id
		FROM
		assignees a2
		JOIN task t2 ON ( a2.task_id = t2.ID )
		GROUP BY
		t2.assignment_id
		) a2 ON a2.assignment_id = A.ID
		where T.created_at BETWEEN '2022-01-01 16:46:21' AND '2022-12-31 16:46:21' and (LOWER(s.name) LIKE
		LOWER('%keuangan%') or LOWER(ss.name) LIKE LOWER('%keuangan%'))

		GROUP BY
		s.ID,
		ss.ID,
		T.ID,
		tsh.closed_time,
		a2.deadline,
		T.created_at
		) A
		) b
		WHERE
		tanggal IN ( SELECT work_date FROM calender_work_day WHERE is_work_day = 0 )
		-- AND ticket_created = '2022-01-01'

		GROUP BY
		service,
		sub_service,
		sla,
		ticket_created,
		closed_time
		ORDER BY
		4 ASC
		) b ON A.service = b.service
		AND A.sub_service = b.sub_service
		AND A.sla = b.sla
		and a.ticket_created = b.ticket_created
		and a.closed_time = b.closed_time
		GROUP BY
		A.service,
		A.sub_service,
		A.ticket_created,
		A.closed_time,
		lama_seluruh_hari,
		jumlah_libur,
		tanggal,
		A.sla,
		is_late )
		Sd
		GROUP BY service , sub_service , sla
		ORDER BY 1
Editor is loading...