My boss wanted some stats how on the staff was processing tickets. For a fast and readable report, I connected MS Excel via ODBC and pasted in this query. It gives a table of users, total tickets, open tickets, closed tickets, and average time in minutes between when a ticket was created and closed.
SELECT a.username,
COUNT(b.ticket_id) ,
SUM(IF (b.status = 'open', 1, 0)) ,
SUM(IF (b.status = 'closed', 1, 0)) ,
ROUND(AVG(TIME_TO_SEC(TIMEDIFF(b.closed, b.created))) / 60, 0)
FROM ost_staff a, ost_ticket b
WHERE a.staff_id = b.staff_id
GROUP BY 1
ORDER BY a.lastname, a.firstname
For whatever reason, converting minutes to days/hours/minutes in the MySQL query returned bogus values for only certain records. I fixed that up in Excel by using:
=INT(E4/60/24)&"d " & TEXT(MOD(E4/60/24,1),"hh\h mm\m")
Where E4 is the average minutes field.
If you don't mind your reporting being in Excel, you could take that query above and build on it for departments, etc. Maybe later paste it into a nice PHP page later.