View Reporting layanan query

  select 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 
	s.NAME AS service,
	ss.NAME AS sub_service,
 ss.sla,
	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 badingin,
	(
		EXTRACT (
			EPOCH 
		FROM
			tsh.closed_time - (
			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 
					) 
				) / 3600 
			) - (( ( date_part( 'day', tsh.closed_time - T.created_at ) :: NUMERIC ) - 1 ) * 15.5) AS spend_hours
			
		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 
             
			
		GROUP BY
			s.ID,
			ss.ID,
			T.ID,
			tsh.closed_time,
		a2.deadline,
	T.created_at
	) a
	      group by service , sla