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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

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.

Answered 06/25/2012 by: nshah
Red Belt

Please log in to comment
3

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

Answered 06/26/2012 by: Tentacle Master
Fourth Degree Black Belt

Please log in to comment
0

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.

Answered 06/25/2012 by: jbigman3122
Senior Yellow Belt

Please log in to comment
0

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

Answered 06/30/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity