I need help creating a report that just shows the Owner and how many tickets they closed the past 7 days.
I did find exactly what i am looking for on the forum except i dont know how to add just show the tickets the past 7 days and not lifetime. Here is what i have right now (THANKS TO AIRWOLF)
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets' FROM HD_TICKET T JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID) LEFT JOIN USER O ON (T.OWNER_ID = O.ID) GROUP BY O.FULL_NAME
Answers (4)
The report already in the KBOX named "Closed Tickets last 7 days by Owner
" won't work for you? It is under the Helpdesk category.
jbigman3122,
You can either, duplicate the report and trim out the info you don't need if you will continue to need the report. or if this is kinda a one time thing, you can export the report to csv and delete the irrevelant information. I do both depending on the situation
I was trying to get it without all the other stuff. The boss just wants to see how many tickets were closed. That report show everything about the ticket.
To remove columns that you don't need/want, you can just remove them from the SELECT statement at the very beginning of a query.
For other tips on writing/modifying MySQL queries for the K1000, please see the following post:
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
As for the report you want, try this out. You can add columns to your liking, just be aware that if you need to add columns that aren't present in the table referenced in the FROM statement, you'll need to use a JOIN statement (or statements) to add the column(s) you want to display.
Hope that helps!
John
_______________________________
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets'
FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE S.NAME = 'closed' AND DATEDIFF(NOW(),T.TIME_CLOSED) < 7
GROUP BY O.FULL_NAME
ORDER BY O.FULL_NAME