Excluding SLA CLock Time (Hold status) using PL/Sql Query and make MTTR

Question

Hi,

I have a query where I require to exclude "Hold Status" time b/w open to acknowledge status.
So, support I have ticket '9607' and it is in the Open Status(INIT) and assigned. While selecting open to Hold and hold to open and then open to Acknowledge. It Will only calculate the time of open to Acknowledge and exclude the Hold time. So this will be a loop that how many times the Hold status will come.

And the Hold status will be required to catch from act_log table.
And also require to calculate the Mean of all the tickets time_stamp.

Below is the query which is calculating the time_stamp on each Status its showing on Unix time also require to convert it it Days hrs mins seconds

------------------------------------------------------------------------------------------------------------------

Following is the query:

WITH TIMEDIFF AS
(
SELECT call_req.ref_num , call_req.type type1 , ca_contact.first_name, ca_contact.last_name
,ROW_NUMBER() OVER ( ORDER BY ref_num, time_stamp ) as RowNumber ,
act_log.id , act_log.persid , call_req_id , act_log.analyst, SUBSTRING( action_desc , PATINDEX ( '% assignee from %', action_desc ) + 15 , DATALENGTH( action_desc ) -2 ) team ,
act_log.action_desc , act_log.type , dateadd (S,[time_stamp], '1970-01-01') Activity_Date ,act_log.time_stamp
FROM call_req inner join act_log on call_req.persid = act_log.call_req_id inner join ca_contact on act_Log.analyst = ca_contact.contact_uuid
where ca_contact.contact_type='2307' and call_req.type = 'I'
and act_log.type in ( 'TR' , 'INIT','RE','ST' ) and call_req.ref_num ='9607'
)

select cur.id , cur.type1, cur.Activity_Date, cur.persid , cur.call_req_id ,cur.first_name,cur.last_name, cur.ref_num, replace ( left( cur.team ,CHARINDEX( ' to ' , cur.team) ) , '''', '') TeamName , cur.time_stamp ,
cur.type , ( cur.time_stamp - pre.time_stamp ) Time_Taken from TIMEDIFF cur
LEFT OUTER JOIN TIMEDIFF pre on cur.RowNumber = pre.RowNumber +1

-----------------------------------------------------------------------------

I am including the following:

1. Result of the Above query save in txt file but can be open as CSV
2. act_log table result w.r.t ref_number 9607

Please let me know if you require more information from my end. 217821-query-resule.txt217795-act-log-9607-updated.txt

Answer 1

You are apparently asking for a query in Oracle, not Microsoft SQL Server. Your question would be better answered on the Oracle forum.

Answer 2

Sorry for the delay, but below is a query. I will need to add quite a few caveats.

• Since you did not provide the expected results, I have not been able to verify that the result is correct.
• Building logic on a free-text column is precarious. Then again, from your small sample, it seems that the action_desc column is machine generated, so I'm taking my chances that the text is consistent. But you may have to refine that part.
• The solution requires SQL 2012 or later. (I note that you implement LAG in the style we had to do up to SQL 2008.)

  DECLARE @T TABLE (type varchar(10) NOT NULL,  
  action nvarchar(50) NULL,  
  factor tinyint NOT NULL CHECK (factor IN (0, 1)),  
  final bit NOT NULL,  
  UNIQUE (type, action)  
  )  
  INSERT @T (type, action, factor, final)  
  VALUES('INIT', NULL, 1, 0),  
  ('ST', 'Status changed from ''Open'' to ''Hold''', 1, 0),  
  ('ST', 'Status changed from ''Hold'' to ''Open''', 0, 0),  
  ('ST', 'Status changed from ''Open'' to ''Acknowledged''', 1, 1)

  ; WITH curandprev AS (  
  SELECT a.call_req_id, a.time_stamp, t.factor,  
  prevstamp = LAG(a.time_stamp) OVER(PARTITION BY a.call_req_id ORDER BY a.time_stamp),  
  analyst = CASE WHEN t.final = 1 THEN a.analyst END  
  FROM act_log a  
  JOIN @T t ON a.type = t.type  
  AND (convert(nvarchar(MAX), a.action_desc) = t.action OR t.action IS NULL)  
  ), aggr AS (  
  SELECT call_req_id, MAX(analyst) AS analyst, SUM(factor \* (time_stamp - prevstamp)) AS totaltime  
  FROM curandprev  
  GROUP BY call_req_id  
  )  
  SELECT cr.ref_num, concat(ca.first_name, ' ', ca.last_name),  
  days = totaltime / 86400, hhmmss = dateadd(second, totaltime % 86400, convert(time(0), '00:00:00'))  
  FROM aggr a  
  JOIN call_req cr ON a.call_req_id = cr .persid  
  JOIN ca_contact ca ON a.analyst = ca.contact_uuid