View Reporting layanan query
teguhikhlas
pgsql
2 years ago
3.1 kB
7
Indexable
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
Editor is loading...