MySQL query report for Agenwise Month wise count

helpdesksulekha

I need to generate an Mysql query that will give me a pivot of Agent wise month wise closed tickets like the below format.

This query gives me agent wise total count of tickets. but i need the same month wise in columns.
Any help is appreciated

select count(t.num), t.staffname
from (
select osticketdb_ticket.number as num, osticketdb_staff.username as staffname
from osticketdb_ticket
join osticketdb_staff on osticketdb_staff.staff_id = osticketdb_ticket.staff_id
where osticketdb_ticket.status_id = 2 AND osticketdb_staff.username IN('Prakashj','haleemk')
AND osticketdb_ticket.created BETWEEN '2023-04-01' AND '2023-09-30'
) as t
group by t.staffname;

helpdesksulekha

I made this solved with the below query in case anyone wants to use it.

this gives agent wise month wise total tickets count.

(SELECT 'MONTHNAME','name1','name2','name3')
UNION
(SELECT MONTHNAME,name1,name2,name3 FROM
(
SELECT
MONTHNAME(osticketdb_ticket.created) AS MONTHNAME,
MONTH(osticketdb_ticket.created) AS MONTH,
COUNT(CASE WHEN osticketdb_staff.Username = 'Name1' THEN 1 END) AS 'name1',
COUNT(CASE WHEN osticketdb_staff.Username = 'name2' THEN 1 END) AS 'name2',
COUNT(CASE WHEN osticketdb_staff.Username = 'name3' THEN 1 END) AS 'name3',
FROM osticketdb_ticket
join osticketdb_staff on osticketdb_staff.staff_id = osticketdb_ticket.staff_id
WHERE (DATE(osticketdb_ticket.created) BETWEEN DATE('2023-01-01') AND DATE('2023-12-31'))
AND osticketdb_staff.username IN('name1','name2','name3')
GROUP BY MONTH
)X GROUP BY X.MONTH

ntozier

I'm glad that you posted and shared this with the community, thank you.

I would like to point out that using number of ticket closed in a month is not a really good metric.
Why you might ask? because not all tickets are created equally.
(Well unless your tickets are all for the same thing... which would be wierd.)

We added a custom field to our tickets that is time spent (in minutes).
So when we pull the monthly stats I can see that agent1 reported they added X amount of time on tickets that month.
(Note: I still look to see how many tickets that they worked on, but not how many they closed.)
If that staff is a five 8 hour day employee that number should be near 1920 minutes.
Why 1920 and not 2400? 1920 is 80% of 2400. 2400 would be 8 hours * 5 days.
With overhead, admin time, breaks, etc it seems like a more real target to aim for 80% utilization.