Report to track how many tickets are submitted to a department in any given month

[Logman]

I need to be able to quote our ticket volume to an outsourced support supplier. How can I see how many tickets are submitted in any given month for each department?

[Logman]

I found this:

https://marketplace.whmcs.com/product/3877

It's one PHP file but no idea how to use it. Uploaded to modules/addons but nothing shows.

[brian!]

15 minutes ago, Logman said:

It's one PHP file but no idea how to use it. Uploaded to modules/addons but nothing shows.

if it's a report, you upload it to /modules/reports - once you refresh the reports page in the browser, you should see it as one of the available reports to use.

[Logman]

Cheers Brian. Got the option now but it doesn't seem to work properly. For instance no matter what the date range "Linux Support" always returns seven hundred and something. Oh, just tried one day and still got 191 tickets when he had around 5. Any simple MySQL query I can run to get the stats?

[brian!]

23 minutes ago, Logman said:

Any simple MySQL query I can run to get the stats?

to get a monthly ticket count, per department, you could use...

SELECT 
DATE_FORMAT(date, "%Y-%m") as Date,
tblticketdepartments.`name` as Department,
Count(*) as Count
FROM
tbltickets
INNER JOIN tblticketdepartments ON tbltickets.did = tblticketdepartments.id
GROUP BY Department, Date
ORDER BY date ASC

in any months not listed, you can assume that no tickets were created for any department.

if you just need it for a specific month, e.g June 2018

SELECT 
DATE_FORMAT(date, "%Y-%m") as Date,
tblticketdepartments.`name` as Department,
Count(*) as Count
FROM
tbltickets
INNER JOIN tblticketdepartments ON tbltickets.did = tblticketdepartments.id
WHERE
DATE_FORMAT(date, "%Y-%m") = '2018-06'
GROUP BY Department, Date
ORDER BY date ASC

 

[Logman]

Thanks Brian. That gives me an output like this:

Each row can't be a day as this is for June and only 10 days in. Also we have 3 departments in total and the query seems to be pulling from the first default department. Ideally I'd like to see a printout of just the number of tickets in each department (rather than a row per ticket). Don't suppose you have that query up your sleeve somewhere?

Edited June 10, 2018 by Logman

[brian!]

6 minutes ago, Logman said:

Each row can't be a day as this is for June and only 10 days in.

i'm assuming you can get more than one ticket per day!

6 minutes ago, Logman said:

Also we have 3 departments in total and the query seems to be pulling from the first default department.

it shouldn't be.... my guess is that if you scrolled through the results, you'd find other departments listed.

6 minutes ago, Logman said:

Ideally I'd like to see a printout of just the number of tickets in each department (rather than a row per ticket). Don't suppose you have that query up your sleeve somewhere?

there was an error in the "group by" option - try using...

SELECT 
DATE_FORMAT(date, "%Y-%m") as Date,
tblticketdepartments.`name` as Department,
Count(*) as Count
FROM
tbltickets
INNER JOIN tblticketdepartments ON tbltickets.did = tblticketdepartments.id
GROUP BY DATE_FORMAT(date, "%Y-%m"),Department
ORDER BY date ASC

this is what i'm seeing when I run this on a v7.5.1 dev...

[Logman]

Great, so modified for May:

SELECT 
DATE_FORMAT(date, "%Y-%m") as Date,
tblticketdepartments.`name` as Department,
Count(*) as Count
FROM
tbltickets
INNER JOIN tblticketdepartments ON tbltickets.did = tblticketdepartments.id
GROUP BY DATE_FORMAT(date, "%Y-%m") = '2018-06',Department
ORDER BY date ASC

I guess that is accurate, seems about right. Not sure why those first 3 rows are outputted though.

 

Edited June 10, 2018 by Logman

[brian!]

18 minutes ago, Logman said:

Not sure why those first 3 rows are outputted though.

you have a slightly kinky "group by" statement that should be split off into a "where"...

SELECT 
DATE_FORMAT(date, "%Y-%m") as Date,
tblticketdepartments.`name` as Department,
Count(*) as Count
FROM tbltickets
INNER JOIN tblticketdepartments ON tbltickets.did = tblticketdepartments.id
WHERE DATE_FORMAT(date, "%Y-%m") = '2018-05'
GROUP BY DATE_FORMAT(date, "%Y-%m"),Department

hopefully, that will now give you a count for May only... if it's for one month only, you won't need the date sort because they should all have the same month.

[Logman]

Bingo. Thanks mate. Much appreciated.