Sub layanan Excel Query
teguhikhlas
pgsql
2 years ago
6.6 kB
9
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 14:19:03' AND '2022-12-31 14:19:03' and (LOWER(s.name) LIKE LOWER('%celoe%') or LOWER(ss.name) LIKE LOWER('%celoe%'))
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 14:19:03' AND '2022-12-31 14:19:03' and (LOWER(s.name) LIKE LOWER('%celoe%') or LOWER(ss.name) LIKE LOWER('%celoe%'))
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 1Editor is loading...