I followed this to try to get the AHT from my database.
So, the database structure is:
_____________________________________________________________________________________
| |
| ticketid tecnico_id time_at visit_hour_in visit_hour_out |
|_____________________________________________________________________________________|
| 1 3 08:15 16:03 17:00 |
| 2 7 10:45 11:03 13:00 |
| 3 9 08:05 12:03 14:00 |
| 4 6 08:43 12:03 13:30 |
| 5 10 13:30 14:03 15:45 |
|_____________________________________________________________________________________|
Therefore, I would like to get the average handling time by tecnico_id
.
I tried with:
SELECT tecnico_id,
AVG((time_at) - (visit_hour_in)) AS responseTime,
AVG((visit_hour_in) - (visit_hour_out)) AS activityTime,
AVG((time_at) - (visit_hour_out)) AS handlingTime
FROM `ticket` GROUP BY tecnico_id ORDER BY `ticket`.`tecnico_id` ASC
It outputs this:
___________________________________________________________________________
| |
| tecnico_id responseTime activityTime handlingTime |
|___________________________________________________________________________|
| 3 0 0 0 |
| 5 13.6 0 13.6 |
| 6 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 7 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 8 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 9 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 10 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 11 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 18 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 19 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 20 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 21 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 22 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 23 5.791946308724833 -0.37583892617449666 5.416107382550336 |
| 24 5.791946308724833 -0.37583892617449666 5.416107382550336 |
|___________________________________________________________________________|
The 5.791946308724833
, -0.37583892617449666
and 5.416107382550336
are not repeated, I just didn’t wanted to format the table.
To convert it to hours, min and secs I use gmdate('H:i:s', '5.791946308724833')
, (as an example), however… The results are not accurate as they should be. What I need to do, is getting the difference between those fields, then getting the average, so I can say, as an example:
tecnico_id’s #3 responseTime is 02 hours and 3 minutes, his activity time is 0 hours and 48 minutes and his AHT per ticket is 3 hours and 0 minutes.
How can I achieve this?
Advertisement
Answer
The way to solve this is actually easy. I used this code to make it work.
SELECT tecnico_id, COUNT(tecnico_id) as cantidad_tecnico ,
SUM(TIMESTAMPDIFF(MINUTE,convert(time_at,time),convert(visit_hour_in, time))) as tiempo,
(TIMESTAMPDIFF(MINUTE,convert(time_at,time),convert(visit_hour_in, time))/COUNT(tecnico_id)) as promedio
FROM ticket group by tecnico_id asc