Agent first respond time statistics

[gilrim]

I'm trying to get out some statistics on the responsetime by agents, per case. So far I've pulled together the sql you can see below, however I've got the feeling it's overly complicated and inefficiant. could anyone with knowledge on the database tables and fields used by otrs comment?

Code: Select all

SELECT DISTINCT ticket.tn AS TicketNumber
              , ticket.title AS Subject
              , queue.name AS QueueName
              , concat(u2.first_name, " ", u2.last_name) AS TicketOwner
              , ticket.create_time AS CreationTime
              , (SELECT article.create_time
                 FROM
                   article
                 INNER JOIN article_sender_type
                 ON article.article_sender_type_id = article_sender_type.id
                 INNER JOIN article_type
                 ON article.article_type_id = article_type.id
                 INNER JOIN ticket
                 ON article.ticket_id = ticket.id
                 
                 WHERE
                   ticket.tn = TicketNumber
                   AND article_sender_type.name = 'agent'
                 ORDER BY
                   article.id
                 LIMIT
                   1) AS firstAgentResponse
               ,  TIME_FORMAT(SEC_TO_TIME((unix_timestamp((SELECT article.create_time
                 FROM
                   article
                 INNER JOIN article_sender_type
                 ON article.article_sender_type_id = article_sender_type.id
                 INNER JOIN article_type
                 ON article.article_type_id = article_type.id
                 INNER JOIN ticket
                 ON article.ticket_id = ticket.id
                 
                 WHERE
                   ticket.tn = TicketNumber
                   AND article_sender_type.name = 'agent'
                 ORDER BY
                   article.id
                 LIMIT
                   1))-unix_timestamp(ticket.create_time))),'%Hh %im') AS ResponseTime
FROM
  ticket
INNER JOIN article
ON ticket.id = article.ticket_id
INNER JOIN queue
ON ticket.queue_id = queue.id
INNER JOIN article_sender_type
ON article.article_sender_type_id = article_sender_type.id
INNER JOIN article_type
ON article.article_type_id = article_type.id
INNER JOIN users u2
ON ticket.user_id = u2.id
WHERE queue.name != 'Junk'
AND ticket.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
 
ORDER BY ResponseTime DESC

[crythias]

seems reasonable.
Try this:

Code: Select all

select t.tn as TicketNumber, t.title AS Subject, q.name AS Queue, concat(u.first_name, " ", u.last_name) AS Owner, t.create_time AS CreationTime, TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, t.create_time, a.create_time)), '%Hh, %im, %ss') as ResponseTime
from ticket t
left join queue q on (q.id = t.queue_id)
left join users u on (u.id = t.user_id)
left join article a on (t.id = a.ticket_id)
left join article_sender_type ast on (ast.id = a.article_sender_type_id)
left join article filter on (a.ticket_id = filter.ticket_id) and a.create_time > filter.create_time
where filter.id IS NULL and ast.name = "agent"

This only gives you tickets where an agent actually responded, btw.

Let me know if it works or doesn't.

[gilrim]

the query works, but I'm not certain about the the output?
on my two systems, the query I posted above returns 1226 and 76 rows, while yours return 293 and 39 rows respectively. and you don't seem to limit it to be just for tickets created the last month either; I was expecting to see more/the complete article list?

sql isn't my native tongue unfortunately, so I'm having a hard time figuring out what your actually reading?

[crythias]

you don't seem to limit it to be just for tickets created the last month either

I figured you could add that in the query.

I was expecting to see more/the complete article list?

are you sure you don't mean complete ticket list? You only want first response as time.

Basically, this is the first agent response per ticket, just like you want.

This gets all tickets and matches articles where there's a response by an agent and returns the difference between ticket open/create date and the first agent response.

[gilrim]

thanks, just to clarify it: your query, when executed as-is, should return all tickets where a agent have responded, and the time between the ticket was opened and when the response was sent?

if so, then I don't think it's working just yet, as I'm certain we've responded to more than 39 cases with otrs... sorry for being unclear in wording.

[crythias]

What is "293" and "39"?

[gilrim]

What is "293" and "39"?

the number of rows returned when running the query against the two installations I'm working on.. When searching the last of those for all tickets created the last month using otrs search, there are 82 tickets returned. most of those have been responded to by a agent.

[crythias]

I believe I understand the problem and it's related to the first article being not an agent one.

Code: Select all

select t.tn as TicketNumber, t.title AS Subject, q.name AS Queue, concat(u.first_name, " ", u.last_name) AS Owner, t.create_time AS CreationTime, TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, t.create_time, a.create_time)), '%Hh, %im, %ss') as ResponseTime
from ticket t
left join queue q on (q.id = t.queue_id)
left join users u on (u.id = t.user_id)
left join article a on (t.id = a.ticket_id)
left join article_sender_type ast on (ast.id = a.article_sender_type_id)
left join article filter on (a.ticket_id = filter.ticket_id) and a.create_time > filter.create_time and ast.name="agent"
where filter.id IS NULL