rramona2
8/8/2017 - 2:39 AM
dd
dd
SELECT CURRENT_TIMESTAMP() AS last_refresh_date,
'TRT' AS calculation_type,
rtd.ticket_id AS ticket_id,
rtd.status AS status,
rtd.queue AS queue,
IF(NVL(LENGTH(rtd.assignee), 0) == 0, '-', rtd.assignee) AS assignee,
rtd.building AS assignee_building,
rtd.category AS category,
rtd.type AS TYPE,
rtd.item AS item,
CONCAT(rtd.category,'-', rtd.type,'-', rtd.item) AS cti,
rtd.priority AS priority,
rtd.requester AS requester,
t.sla_seconds AS sla_target_secs,
t.sla_minutes AS sla_target_minutes,
t.sla_hours AS sla_target_hrs,
rtd.arrival_time AS ticket_creation_time_pst,
t.resolved_queue_assign_time AS assigned_to_queue_time_pst,
YEAR(t.resolved_queue_assign_time) AS assigned_to_queue_year,
MONTH(t.resolved_queue_assign_time) AS assigned_to_queue_month,
DAY(t.resolved_queue_assign_time) AS assigned_to_queue_day,
CONCAT(STRING(YEAR(t.resolved_queue_assign_time)),'-',STRING(MONTH(t.resolved_queue_assign_time))) AS assigned_to_queue_year_month,
CONCAT(STRING(YEAR(t.resolved_queue_assign_time)),'-',STRING(WEEK(t.resolved_queue_assign_time))) AS assigned_to_queue_year_week,
rtd.resolved_time AS resolved_time_pst,
YEAR(rtd.resolved_time) AS resolved_year,
MONTH(rtd.resolved_time) AS resolved_month,
DAY(rtd.resolved_time) AS resolved_day,
CONCAT(STRING(YEAR(rtd.resolved_time)),'-',STRING(MONTH(rtd.resolved_time))) AS resolved_year_month,
CONCAT(STRING(YEAR(rtd.resolved_time)),'-',STRING(WEEK(rtd.resolved_time))) AS resolved_year_week,
rtd.last_modified_date_pac_tz AS closed_time_pst,
YEAR(rtd.last_modified_date_pac_tz) AS closed_year,
MONTH(rtd.last_modified_date_pac_tz) AS closed_month,
DAY(rtd.last_modified_date_pac_tz) AS closed_day,
CONCAT(STRING(YEAR(rtd.last_modified_date_pac_tz)),'-',STRING(MONTH(rtd.last_modified_date_pac_tz))) AS closed_year_month,
CONCAT(STRING(YEAR(rtd.last_modified_date_pac_tz)),'-',STRING(WEEK(rtd.last_modified_date_pac_tz))) AS closed_year_week,
t.resolved_queue_touches AS total_touches_in_queue,
IFNULL(t.all_queues_ptime, 0) AS total_pending_time_secs,
(TIMESTAMP_TO_USEC(rtd.resolved_time) - TIMESTAMP_TO_USEC(t.resolved_queue_assign_time)) AS trt_msecs,
ROUND(((TIMESTAMP_TO_USEC(rtd.resolved_time) - TIMESTAMP_TO_USEC(t.resolved_queue_assign_time))*0.000001),4) AS trt_secs,
ROUND(((TIMESTAMP_TO_USEC(rtd.resolved_time) - TIMESTAMP_TO_USEC(t.resolved_queue_assign_time))*0.000000016666667),4) AS trt_mins,
ROUND(((TIMESTAMP_TO_USEC(rtd.resolved_time) - TIMESTAMP_TO_USEC(t.resolved_queue_assign_time))*0.000000000277778),4) AS trt_hrs,
FROM
(SELECT gam.ticket_id AS ticket_id,
gam.status AS status,
gam.queue AS queue,
gam.assignee AS assignee,
hpd.department AS building,
hpd.category AS category,
hpd.type AS TYPE,
hpd.item AS item,
gam.priority AS priority,
hpd.summary AS summary,
hpd.requester_login_name_ AS requester,
TIMESTAMP(hpd.arrival_time_pac_tz) AS arrival_time,
hpd.arrival_time AS arrival_time_unix,
gam.resolved_time AS resolved_time,
gam.resolved_date AS resolved_date,
gam.resolved_time_unix AS resolved_time_unix,
hpd.auto_close_time AS auto_close_time_unix,
TIMESTAMP(hpd.auto_close_time_pac_tz) AS auto_close_time,
TIMESTAMP(gam.last_modified_date_pac_tz) AS last_modified_date_pac_tz,
FROM
(SELECT helpdesk_id AS ticket_id,
status AS status,
assigned_to_group AS queue,
assignee_login_name AS assignee,
priority AS priority,
TIMESTAMP(last_resolved_time_pac_tz) AS resolved_time,
TIMESTAMP(DATE(last_resolved_time_pac_tz)) AS resolved_date,
last_resolved_time AS resolved_time_unix,
modified_date_pac_tz AS last_modified_date_pac_tz
FROM [google.com:gutsreports:gutstobq_public.GOOGLE_AUDIT_MASTER_VIEW]
WHERE status = 'Closed'
AND assigned_to_group = 'gtech-risk-team') gam
INNER JOIN EACH [google.com:gutsreports:gutstobq_public.HPD_HELPDESK_VIEW] hpd ON gam.ticket_id = hpd.case_id_) rtd
INNER JOIN EACH
(SELECT gam.helpdesk_id AS ticket_id,
MIN(TIMESTAMP(IF((gatm.sla_audit_id = '000000000000002')
AND (gam.assigned_to_group == gatm.assigned_to_group), gatm.start_time_pac_tz, '9999-12-31'))) AS resolved_queue_assign_time,
SUM(IF((sla_audit_id = '000000000000002')
AND (gam.assigned_to_group == gatm.assigned_to_group), time_elapsed, 0)) AS resolved_queue_resolution_secs,
SUM(IF(sla_audit_id = '000000000000002', time_elapsed, 0)) AS creation_to_resolution_secs,
SUM(IF((sla_audit_id = '000000000000003')
AND (gam.assigned_to_group == gatm.assigned_to_group), 1, 0)) AS resolved_queue_touches,
SUM(IF(sla_audit_id = '000000000000003', 1, 0)) AS all_queue_touches,
SUM(IF((sla_audit_id = '000000000000008')
AND (gam.assigned_to_group == gatm.assigned_to_group), gatm.time_elapsed, 0)) AS resolved_queue_ptime,
SUM(IF((sla_audit_id = '000000000000008'), gatm.time_elapsed, 0)) AS all_queues_ptime,
MIN(sla.sla_seconds) AS sla_seconds,
MIN(sla.sla_minutes) AS sla_minutes,
MIN(sla.sla_hours) AS sla_hours
FROM
(SELECT helpdesk_id,
assigned_to_group,
start_time_pac_tz,
sla_audit_id,
time_elapsed
FROM [google.com:gutsreports:gutstobq_public.GOOG_AUDIT_TIME_MEASUREMENT_VIEW]
WHERE sla_audit_id IN ('000000000000002',
'000000000000003',
'000000000000008')) gatm
INNER JOIN EACH
(SELECT helpdesk_id,
assigned_to_group,
priority
FROM [google.com:gutsreports:gutstobq_public.GOOGLE_AUDIT_MASTER_VIEW]
WHERE status = 'Closed'
AND (assigned_to_group = 'gtech-risk-team')) gam ON gatm.helpdesk_id = gam.helpdesk_id
LEFT JOIN EACH
(SELECT group_name,
priority,
sla_type,
sla_seconds,
sla_minutes,
sla_hours,
FROM [google.com:gtech-risk-ops:gtech_risk_team.ref_sla]
WHERE sla_type = 'Resolution') sla ON gam.priority = sla.priority
AND gam.assigned_to_group = sla.group_name
GROUP BY ticket_id) t ON rtd.ticket_id = t.ticket_id
LEFT JOIN EACH
(SELECT department_short AS department_short,
orgstore_status AS orgstore_status,
orgstore_country_code AS orgstore_country_code,
orgstore_code AS orgstore_code
FROM [google.com:gutsreports:gutstobq_public.SHR_LOCATION_VIEW]
GROUP EACH BY department_short,
orgstore_status,
orgstore_country_code,
orgstore_code) sl ON rtd.building = sl.department_short
ORDER BY rtd.last_modified_date_pac_tz DESC