143 lines
7.0 KiB
SQL
143 lines
7.0 KiB
SQL
SELECT
|
|
s.TicketNbr AS 'id'
|
|
,s.TicketNbr AS 'Ticket_Number'
|
|
,s.company_name AS 'Company_Name'
|
|
,co.company_id AS 'Company_ID'
|
|
,s.contact_name AS 'Contact'
|
|
,s.source AS 'Source'
|
|
,s.Site_Name AS customer_site
|
|
,s.team_name
|
|
,s.Territory
|
|
,s.location AS 'Location'
|
|
,s.board_name AS 'Board'
|
|
,s.summary AS 'Summary'
|
|
,s.status_description AS 'Status'
|
|
,CAST(s.date_entered AS DATETIME) AS 'date_opened'
|
|
,CAST(s.last_update AS DATETIME) AS 'date_last_updated'
|
|
,CAST(s.Date_Required AS DATETIME) AS 'Date_Required'
|
|
,COALESCE(s.Responded_Minutes,0) + COALESCE(s.Responded_Skipped_Minutes,0) AS 'time_to_acknowledgement_minutes'
|
|
,CASE
|
|
WHEN s.Date_Responded_UTC is NULL THEN NULL
|
|
ELSE COALESCE(s.Responded_Minutes,0) + COALESCE(s.Responded_Skipped_Minutes,0)
|
|
END AS 'time_to_response_minutes'
|
|
,CAST(s.date_responded_utc AS DATETIME) AS acknowledgement_date
|
|
,CAST(s.date_responded_utc AS DATETIME) AS response_date
|
|
,CASE WHEN s.Date_Responded_UTC IS NOT NULL THEN (CASE WHEN s.Responded_Minutes + s.Responded_Skipped_Minutes <=
|
|
(CASE WHEN slap.Responded_Hours IS NOT NULL THEN slap.Responded_Hours ELSE sla.Responded_Hours END
|
|
* 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresponsesla'
|
|
,CAST(CAST (s.Resplan_Minutes + s.Resplan_Skipped_Minutes + s.Responded_Minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'time_to_resolution_plan(hours)'
|
|
,CAST(s.date_resplan_utc AS DATETIME) AS 'resolution_plan_date'
|
|
,CASE WHEN s.Date_Resplan_UTC IS NOT NULL THEN (CASE WHEN s.Resplan_Minutes + s.Resplan_Skipped_Minutes + s.Responded_Minutes <=
|
|
(CASE WHEN slap.Resplan_Hours IS NOT NULL THEN slap.Resplan_Hours ELSE sla.Resplan_Hours END
|
|
* 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresplansla'
|
|
,CAST(CAST (s.Resolved_Minutes + s.Resplan_Minutes + s.Responded_Minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'time_to_resolution(hours)'
|
|
,CAST(s.date_resolved_utc AS DATETIME) AS 'resolution_date'
|
|
,CASE WHEN s.Date_Resolved_UTC IS NOT NULL THEN (CASE WHEN s.Resolved_Minutes + s.resplan_minutes + s.Responded_Minutes <=
|
|
(CASE WHEN slap.Resolution_Hours IS NOT NULL THEN slap.Resolution_Hours ELSE sla.Resolution_Hours END
|
|
* 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'metresolutionsla'
|
|
,CAST(s.date_closed AS DATETIME) AS 'date_closed'
|
|
,CASE
|
|
When DATEDIFF(DD, s.date_entered, s.date_closed) = 0 Then 'Y'
|
|
ELSE 'N'
|
|
END AS 'same_day_close'
|
|
,CASE
|
|
WHEN DATEDIFF(DD,s.Date_Responded_UTC,s.Date_Resolved_UTC) = 0 Then 'Y'
|
|
ELSE 'N'
|
|
END AS 'same_day_resolved'
|
|
,s.servicetype AS 'Type'
|
|
,s.servicesubtype AS 'SubType'
|
|
,s.servicesubtypeitem AS 'Service_Item'
|
|
,s.urgency AS 'Priority'
|
|
,s.Severity
|
|
,s.Impact
|
|
,s.Hours_Actual
|
|
,s.Hours_Budget
|
|
,s.Hours_Scheduled
|
|
,s.Hours_Billable
|
|
,s.Hours_NonBillable
|
|
,s.Hours_Invoiced
|
|
,s.Hours_Agreement
|
|
,s.agreement_name
|
|
,CASE WHEN s.Date_Resolved_UTC IS NOT NULL THEN CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, s.Date_Resolved_UTC)/24.0, 0) AS NUMERIC)
|
|
ELSE CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) END AS 'Age (Days)'
|
|
,CASE WHEN s.Date_Resolved_UTC IS NULL THEN
|
|
CASE WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 7 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 15 THEN '2. 1 Week'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 14 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 22 THEN '3. 2 Weeks'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 21 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 30 THEN '4. 3 Weeks'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
|
|
END
|
|
ELSE 'Resolved' END AS 'Unresolved Age (Weeks)'
|
|
,CASE WHEN s.date_closed IS NULL THEN
|
|
CASE WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 7 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 15 THEN '2. 1 Week'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 14 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 22 THEN '3. 2 Weeks'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 21 AND CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 30 THEN '4. 3 Weeks'
|
|
WHEN
|
|
CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
|
|
END
|
|
ELSE 'Resolved' END AS 'Unsolved Age (Weeks)'
|
|
,CASE
|
|
WHEN s.date_responded_utc is NULL THEN '1. Pending Response'
|
|
WHEN s.Date_Resplan_UTC is NULL THEN '2. Pending Resolution Plan'
|
|
WHEN s.Date_Resolved_UTC is NULL THEN '3. Pending Resolution'
|
|
WHEN s.Date_Resolved_UTC is NOT NULL THEN '4. Resolved'
|
|
WHEN s.date_closed is NOT NULL THEN '5. Closed'
|
|
END AS SLA_Escalation_Status
|
|
,LOWER(s.resolved_by) AS Resolved_By
|
|
,LOWER(s.closed_by) AS Closed_By
|
|
,LOWER(s.Responded_By) AS responded_by
|
|
,LOWER(town.member_id) AS Ticket_Owner_ID
|
|
,CAST(town.First_Name AS varchar) + ' ' + cast(town.Last_Name AS varchar) AS Ticket_Owner
|
|
,CASE
|
|
WHEN (s.date_resolved_utc IS NOT NULL) THEN 'Resolved'
|
|
ELSE 'Open'
|
|
END AS Resolved_Flag
|
|
,CASE
|
|
WHEN (s.Closed_Flag = 'True') THEN 'Closed'
|
|
ELSE 'Open'
|
|
END AS Closed_Flag
|
|
,CASE
|
|
WHEN (SELECT recid FROM Schedule
|
|
WHERE close_flag != 'True'
|
|
AND Schedule_Type_RecID = 4
|
|
AND s.ticketnbr = RecID
|
|
GROUP BY recid) IS NOT NULL THEN 'Y'
|
|
ELSE 'N'
|
|
END AS Is_Assigned
|
|
,CASE
|
|
WHEN s.config_recids IS NULL THEN 'False'
|
|
ELSE 'True'
|
|
END AS Config_Attached
|
|
,COALESCE((SELECT COUNT(t.time_recid) FROM time_entry t WHERE t.sr_service_recid = s.sr_service_recid),0) AS Time_Entry_Count
|
|
,al.agr_type_desc AS agreement_type
|
|
,al.Agreement_Status
|
|
,CAST(CASE
|
|
WHEN s.Closed_Flag = 'True' THEN NULL
|
|
ELSE (SELECT MIN(Date_Time_Start_UTC)
|
|
FROM schedule
|
|
WHERE recid = s.sr_service_Recid
|
|
AND schedule_type_recid = 4
|
|
AND close_flag = 'False')
|
|
END AS DATETIME) AS next_date
|
|
FROM v_rpt_service AS s
|
|
LEFT JOIN company AS co ON s.company_recid = co.company_recid
|
|
LEFT JOIN SR_SLA AS sla ON s.SR_SLA_RecID = sla.SR_SLA_RECID
|
|
LEFT JOIN SR_Urgency AS sru ON s.SR_Urgency_RecID = sru.SR_Urgency_RecID
|
|
LEFT JOIN SR_SLAPriority AS slap ON s.SR_SLA_RecID = slap.SR_SLA_RecID AND sru.SR_Urgency_RecID = slap.SR_Urgency_RecID
|
|
LEFT JOIN member AS town ON town.member_recid = s.Ticket_Owner_RecID
|
|
LEFT JOIN v_rpt_agreementlist AS al ON al.agr_header_recid = s.agr_header_recid
|
|
INNER JOIN SR_Service AS sr ON s.ticketnbr = sr.sr_service_Recid
|
|
|
|
WHERE
|
|
(DATEADD(DAY, -210 , Current_Timestamp) <= sr.Last_Update)
|
|
AND sr.Parent_Recid is null |