Calculation of delta between timestamps needed

Moderator: crythias

Post Reply
shlomi
Znuny newbie
Posts: 1
Joined: 15 May 2017, 15:20
Znuny Version: 2.4.5
Real Name: Shlomi Ben-Ami

Calculation of delta between timestamps needed

Post by shlomi »

Hi everyone,

I use SQL to run scheduled queries to get some performance statistics on my agents.
One of those queries gives me a list of agent activity of the current day, refreshes every 5 minutes.
Basically a list of all closed tickets, grouped per agent, for the current day.

I gives a list of the agent name, the date (will always be CURDATE), the time (the closed time), the queue it was in, the ticket number, the ticket creation time and the ticket age at the time it was replied to.

One important thing for measuring agent performance, is to know the delta inbetween closing tickets. E.g. an agent closed a ticket at 10.30am and then the next ticket at 11.10am - which is a delta of 40 minutes. This can be OK, in case the ticket was complex and the agent needed a lot of research time. But in general, it is too long. So I want to add a column to that resulting query that gives me the plain delta minute number between closing tickets.

See attachment, where I highlighted in red the column that shows the closing time of a ticket. Right next to it I want to show the delta in minutes to the previous tickets. In that example you see the last ticket 12.03:29 and the one before 11.33:33. I want right next to it to see the delta of 33 (minutes).

How can I achieve that?

Just FYI, this is my current query:
SELECT CONCAT(users.first_name, ' ', users.last_name) AS Agent, DATE(ticket_history.create_time) AS Date, TIME(ticket_history.create_time) AS Time, queue.name AS Queue, ticket.tn AS Ticket, DATE(ticket.create_time) AS 'Ticket Create Time', TIMESTAMPDIFF(DAY, CURDATE(),DATE(ticket.create_time)) AS 'Ticket Age'
FROM ticket_history
JOIN users ON users.ID = ticket_history.owner_id
JOIN ticket ON ticket.id = ticket_history.ticket_id
JOIN queue ON queue.id = ticket_history.queue_id
WHERE history_type_id IN (27)
AND state_id IN (2,11)
AND ticket_history.owner_id IN ("HERE IS THE LIST OF USER IDs OF MY AGENTS")
AND DATE(ticket_history.create_time) = CURDATE()
ORDER BY users.last_name, ticket_history.create_time ASC;
You do not have the required permissions to view the files attached to this post.
Post Reply