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

Question

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

Mohit Trehan 1

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:

Result of the Above query save in txt file but can be open as CSV
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.txt 217795-act-log-9607-updated.txt

Mohit Trehan 1 Reputation point

2022-07-05T14:52:07.543+00:00

you can download the attached file and change the format to xls for better alignment of the exported sql query result
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-05T15:19:50.47+00:00
For this of question, it is a very good idea to include CREATE TABLE statements for your table(s), and INSERT statements with sample data, enough to illustrate all angles of the problem. To this, we also like to see the expected results given the sample data.

There are two reasons why this is advantageous:

It helps to clarify ambiguities in the verbal description.

It makes it simple to copy and paste into a query window to develop a tested solution.

By the way, the SQL dialect in SQL Server is T-SQL. PL/SQL as you have in your title, is Oracle's dialect.
Mohit Trehan 1 Reputation point

2022-07-05T15:35:19.017+00:00

Thanks for the advise and response. I have attached the default table call_req, ca_contact, and act_log table for the reference. Results of the above pasted query already included previously with the act_log table results for the ref-num 9607.217767-call-req-table.txt 217806-act-log-table.txt 217796-ca-contact-table.txt
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-05T21:04:23.287+00:00

That's the tables. However, there is no data to work from. Note that you don't have to include your full tables. We only need as much data that we can understand the issue. 20 rows in any table would be "many" in this context.

Also, I would recommend that you cut down the CREATE TABLE scripts to only have the columns that are involved in the problem. I also strongly recommend that you test the script before you share it. That is, you make sure that the script runs without errors.

Keep in mind that you are asking people to spend their free time to help you. So please help us to help you!
LiHong-MSFT 10,061 Reputation points

2022-07-06T08:00:23.693+00:00

Hi @Mohit Trehan

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.

I can't understand what you mean without any sample datas. Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

Best regards,
LiHong
Mohit Trehan 1 Reputation point

2022-07-12T10:01:55.367+00:00

Thanks LiHong for the response. Enclosed is the Table with sample Data. 219867-call-req.txt<REDACTED>219768-ca-contact.txt

Where act_Log.analyst = ca_contact.contact_uuid and call_req.persid = act_log.call_req_id

Output Result
To calculate the Time Spent by analyst from open to Acknowledge which is appearing in the act_log table under action_desc field. Where condition is if the type SLADELAY then exclude the timestamp of SLADELAY. And display the timestamp in Days hrs min seconds

Do let me know if more information is required from my end.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-13T21:30:14.157+00:00

I started to look at this, but I'm short on time. Is there any chance that you can show the expected result given the sample data?

I tried the query in your original post, but it did not return any rows. I found that this was due to the condition and call_req.ref_num ='9607' There is now with that value in ref_num in the sample data.
Mohit Trehan 1 Reputation point

2022-07-14T06:43:24.303+00:00

Expected result is like :
Ref_num | Assignee (First & last Name) | Time Stamp

Where Timestamp will give total count of timestamp from Init to Acknowledge excluding the Open to hold timestamp. And if the open to hold time stamp reoccur many times then it will exclude that too.
Time stamp will be in Days hrs min seconds

Do let me know if you require more information.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-14T21:16:51.543+00:00

It would help to have the actual values.

In any case, I don't have more time today than I had yesterday. (I'm on a short vacation trip, so I give priority to the local experience.)

By the way which version of SQL Server do you need this on?
LiHong-MSFT 10,061 Reputation points

2022-08-01T08:33:51.977+00:00

Hi @Mohit Trehan
Have you validate Erland's answer so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

3 answers

Your answer

Mohit Trehan 1 Reputation point

2022-07-05T14:52:07.543+00:00

you can download the attached file and change the format to xls for better alignment of the exported sql query result
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-05T15:19:50.47+00:00

For this of question, it is a very good idea to include CREATE TABLE statements for your table(s), and INSERT statements with sample data, enough to illustrate all angles of the problem. To this, we also like to see the expected results given the sample data.

There are two reasons why this is advantageous:

It helps to clarify ambiguities in the verbal description.

It makes it simple to copy and paste into a query window to develop a tested solution.

By the way, the SQL dialect in SQL Server is T-SQL. PL/SQL as you have in your title, is Oracle's dialect.
Mohit Trehan 1 Reputation point

2022-07-05T15:35:19.017+00:00

Thanks for the advise and response. I have attached the default table call_req, ca_contact, and act_log table for the reference. Results of the above pasted query already included previously with the act_log table results for the ref-num 9607.217767-call-req-table.txt 217806-act-log-table.txt 217796-ca-contact-table.txt
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-05T21:04:23.287+00:00

That's the tables. However, there is no data to work from. Note that you don't have to include your full tables. We only need as much data that we can understand the issue. 20 rows in any table would be "many" in this context.

Also, I would recommend that you cut down the CREATE TABLE scripts to only have the columns that are involved in the problem. I also strongly recommend that you test the script before you share it. That is, you make sure that the script runs without errors.

Keep in mind that you are asking people to spend their free time to help you. So please help us to help you!
LiHong-MSFT 10,061 Reputation points

2022-07-06T08:00:23.693+00:00

Hi @Mohit Trehan

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.

I can't understand what you mean without any sample datas. Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

Best regards,
LiHong
Mohit Trehan 1 Reputation point

2022-07-12T10:01:55.367+00:00

Thanks LiHong for the response. Enclosed is the Table with sample Data. 219867-call-req.txt<REDACTED>219768-ca-contact.txt

Where act_Log.analyst = ca_contact.contact_uuid and call_req.persid = act_log.call_req_id

Output Result
To calculate the Time Spent by analyst from open to Acknowledge which is appearing in the act_log table under action_desc field. Where condition is if the type SLADELAY then exclude the timestamp of SLADELAY. And display the timestamp in Days hrs min seconds

Do let me know if more information is required from my end.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-13T21:30:14.157+00:00

I started to look at this, but I'm short on time. Is there any chance that you can show the expected result given the sample data?

I tried the query in your original post, but it did not return any rows. I found that this was due to the condition and call_req.ref_num ='9607' There is now with that value in ref_num in the sample data.
Mohit Trehan 1 Reputation point

2022-07-14T06:43:24.303+00:00

Expected result is like :
Ref_num | Assignee (First & last Name) | Time Stamp

Where Timestamp will give total count of timestamp from Init to Acknowledge excluding the Open to hold timestamp. And if the open to hold time stamp reoccur many times then it will exclude that too.
Time stamp will be in Days hrs min seconds

Do let me know if you require more information.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-14T21:16:51.543+00:00

It would help to have the actual values.

In any case, I don't have more time today than I had yesterday. (I'm on a short vacation trip, so I give priority to the local experience.)

By the way which version of SQL Server do you need this on?
LiHong-MSFT 10,061 Reputation points

2022-08-01T08:33:51.977+00:00

Hi @Mohit Trehan
Have you validate Erland's answer so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

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

Answer 3

Tom Phillips 17,786

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

Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2022-07-12T20:02:48.363+00:00

Tom, I thought so too, when I read PL/SQL. But the query uses dateadd, patindex and other famous SQL Server functions.
Mohit Trehan 1 Reputation point

2022-07-13T10:57:20.647+00:00

It's an Microsoft SQL query. I changed the tag earlier. Do let me know if the sql-server-transact-sql is the correct tag or not?

Share via

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

3 answers

Your answer