SHARE
TWEET

Untitled

a guest May 23rd, 2019 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. NEWID() as 'id',
  3. s.TicketNbr as 'Ticket_Number',
  4. s.company_name AS 'Company_Name',
  5. co.company_id as 'Company_ID',
  6. s.contact_name AS 'Contact',
  7. s.source AS 'Source',
  8. s.team_name,
  9. s.Territory,
  10. s.location AS 'Location',
  11. s.board_name AS 'Board',
  12. s.summary AS 'Summary',
  13. pr.ProjectName,
  14. s.status_description AS 'Status',
  15. DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CAST(s.date_entered AS DATETIME)) AS 'date_opened',
  16. DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CAST(s.last_update AS DATETIME))  AS 'date_last_updated',
  17. DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CAST(s.Date_Required AS DATETIME))  AS 'Date_Required',
  18. COALESCE (slaw.Responded_Minutes + slaw.Responded_skipped_minutes, 0) AS 'Time_to_Acknowledgement(Minutes)',
  19. CAST(s.date_responded_utc AS DATETIME) AS 'acknowledgement_date',
  20. CASE WHEN slaw.Date_Responded_UTC IS NOT NULL THEN (CASE WHEN slaw.Responded_Minutes + slaw.Responded_skipped_minutes <=
  21. (CASE WHEN slap.Responded_Hours IS NOT NULL THEN slap.Responded_Hours ELSE sla.Responded_Hours END
  22.  * 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'MetResponseSLA',
  23. CAST(CAST (slaw.Resplan_Minutes + slaw.Resplan_Skipped_Minutes + slaw.Responded_Minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'Time_to_Resolution_Plan(Hours)',
  24. CAST(s.date_resplan_utc AS DATETIME) AS 'resolution_plan_date',
  25. CASE WHEN slaw.Date_Resplan_UTC IS NOT NULL THEN (CASE WHEN slaw.Resplan_Minutes + slaw.Resplan_Skipped_Minutes + slaw.Responded_Minutes <=
  26. (CASE WHEN slap.Resplan_Hours IS NOT NULL THEN slap.Resplan_Hours ELSE sla.Resplan_Hours END
  27.  * 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'MetResPlanSLA',
  28. CAST(CAST (slaw.Resolved_Minutes + slaw.resplan_minutes + slaw.responded_minutes AS DECIMAL (9, 2)) / 60.0 AS DECIMAL(10,2)) AS 'Time_to_Resolution(Hours)',
  29. CAST(s.date_resolved_utc AS DATETIME) AS 'resolution_date',
  30. CASE WHEN slaw.Date_Resolved_UTC IS NOT NULL THEN (CASE WHEN slaw.Resolved_Minutes  + slaw.resplan_minutes + slaw.responded_minutes <=
  31. (CASE WHEN slap.Resolution_Hours IS NOT NULL THEN slap.Resolution_Hours ELSE sla.Resolution_Hours END
  32. * 60) THEN 'Met' ELSE 'Unmet' END) ELSE NULL END AS 'MetResolutionSLA',
  33. DATEDIFF(HH, s.date_entered, s.date_closed) AS 'Hours Duration',
  34. DATEDIFF(HH, s.date_entered, s.date_closed)/24 AS 'Days Duration',
  35. DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CAST(s.date_closed AS DATETIME))  AS 'date_closed',
  36. LOWER(s.resolved_by) AS 'Resolved_By',
  37. LOWER(s.closed_by) AS 'Closed_By',
  38. CASE
  39. When DATEDIFF(DD, s.date_entered, s.date_closed) = 0 Then 'Y'
  40. ELSE 'N'
  41. End as 'Same_day_close',
  42. CASE
  43. WHEN DATEDIFF(DD,s.Date_Responded_UTC,s.Date_Resolved_UTC) = 0 Then 'Y'
  44. ELSE 'N'
  45. End as 'Same_day_resolved',
  46. s.servicetype AS 'Type',
  47. s.servicesubtype AS 'SubType',
  48. s.servicesubtypeitem AS 'Service_Item',
  49. s.urgency AS 'Priority',
  50. s.Severity,
  51. s.Impact,
  52. s.Hours_Actual,
  53. s.Hours_Actual*60 as minutes_actual,
  54. s.Hours_Budget,
  55. s.Hours_Scheduled,  
  56. s.Hours_Billable,  
  57. s.Hours_NonBillable,    
  58. s.Hours_Invoiced,  
  59. s.Hours_Agreement,
  60. (s.Hours_Budget - s.Hours_Actual) as 'Hours_Left',
  61. s.agreement_name,
  62. mk.description as 'market',
  63. CASE WHEN slaw.Date_Resolved_UTC IS NOT NULL THEN CAST(ROUND(DATEDIFF(Hour, s.Date_Entered,    slaw.Date_Resolved_UTC)/24.0, 0) AS NUMERIC)
  64. ELSE CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) END AS 'Age (Days)',
  65.  
  66. CASE WHEN slaw.Date_Resolved_UTC IS NULL THEN  
  67.     CASE WHEN
  68.     CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
  69.     WHEN
  70.     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'
  71.     WHEN
  72.     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'
  73.     WHEN
  74.     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'
  75.     WHEN
  76.     CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
  77.     END
  78. ELSE 'Resolved' END AS 'Unresolved Age (Weeks)' ,
  79. CASE WHEN s.date_closed IS NULL THEN  
  80.     CASE WHEN
  81.     CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) < 8 THEN '1. Current'
  82.     WHEN
  83.     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'
  84.     WHEN
  85.     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'
  86.     WHEN
  87.     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'
  88.     WHEN
  89.     CAST(ROUND(DATEDIFF(Hour, s.Date_Entered, CURRENT_TIMESTAMP)/24.0, 0) AS NUMERIC) > 29 THEN '5. 1+ Month'
  90.     END
  91. ELSE 'Resolved' END AS 'Unsolved Age (Weeks)' ,
  92.  
  93. CASE
  94.     WHEN (s.date_resolved_utc IS NOT NULL) THEN 'Resolved'
  95.     ELSE 'Open'
  96.   END AS 'Resolved_Flag',
  97. CASE
  98.     WHEN (s.Date_Closed IS NOT NULL) THEN 'Closed'
  99.     ELSE 'Open'
  100. END AS 'Closed_Flag',
  101.   CASE
  102.     WHEN (sch.RecID IS NOT NULL) THEN 'Y'
  103.     ELSE 'N'
  104.   END AS 'Is_Assigned',
  105.  
  106. sr.CustUpdate_Flag as 'Customer_Responded',
  107. ISNULL(time.time_entry_count, 0) AS 'Time Entry Count',
  108. CASE WHEN (SELECT top 1 c.sr_service_recid FROM SR_Config c
  109. WHERE s.ticketnbr = c.sr_service_recid) IS NULL THEN 'False'
  110. ELSE 'True' END AS 'Config_Attached',
  111. CAST(v_SR_Service_Custom_Fields.[EST $$ Value] AS float) as 'Est $$ Value',
  112. CAST(v_SR_Service_Custom_Fields.[ETA] AS date) as 'ETA',
  113. CAST(v_SR_Service_Custom_Fields.[Looper] AS bit) as 'Looper',
  114. CAST(v_SR_Service_Custom_Fields.[Review Requested] AS bit) as 'Review Requested'
  115.  
  116. FROM v_rpt_service AS s
  117. LEFT JOIN company as co on s.company_recid = co.company_recid
  118. LEFT JOIN market as mk on mk.Market_RECID = co.Market_RECID
  119. LEFT JOIN (SELECT RecID FROM Schedule GROUP BY RecID) sch ON s.ticketnbr = sch.RecID
  120. LEFT JOIN v_rpt_Project AS pr on pr.SR_Service_RecID = s.sr_service_Recid
  121. LEFT JOIN SR_Service_SLA_Workflow AS slaw ON s.ticketnbr = slaw.SR_Service_RecID
  122. LEFT JOIN SR_Service AS sr ON s.ticketnbr = sr.sr_service_Recid
  123. LEFT JOIN SR_Urgency AS sru ON sr.SR_Urgency_RecID = sru.SR_Urgency_RecID
  124. LEFT JOIN SR_SLA AS sla ON sr.SR_SLA_RECID = sla.SR_SLA_RECID
  125. LEFT JOIN SR_SLAPriority AS slap ON sr.SR_SLA_RecID = slap.SR_SLA_RecID AND sru.SR_Urgency_RecID = slap.SR_Urgency_RecID
  126. LEFT JOIN (SELECT SR_Service_RecID, COUNT(SR_Service_Recid) AS 'time_entry_count' FROM Time_Entry GROUP BY SR_Service_RecID) time on s.TicketNbr = time.SR_Service_RecID
  127. LEFT JOIN v_SR_Service_Custom_Fields ON s.TicketNbr = v_SR_Service_Custom_Fields.SR_Service_RecID
  128. LEFT JOIN v_rpt_ServiceCustomFields AS cf on cf.SR_Service_RecID = sr.sr_service_Recid
  129.  
  130. WHERE
  131. (DATEDIFF(DAY, s.Last_Update, Current_Timestamp) <= 120)
  132. AND s.parent is null
  133. and s.sr_board_Recid not in (1,6,21,31,33,39,40,41,44,35,49)
  134. -- and s.sr_board_Recid not in (38)
  135. and DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), CAST(s.date_entered AS DATETIME)) >= DATEADD(d,-730,GETDATE())
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top