Report for tickets "Created by"
I am looking for a way to be able to tell how many tickets a helpdesk user has created in the last year. I can report on closed ticket, but I am looking for the number that they created. We have multiple queues that tickets could have been created under, so Id like it to look at all of the queues. The timeframe I am looking for is in the past 365 days.
I know I talked to a report writer in our org a while ago and he was able to see that on the back end "Created by" was just something tied in with a standard ticket update. Like there isn't a field called "CREATED_BY" or anything like that, it just shows up as a ticket update with a flag set for "Created by". I hope that makes sense.
I am ultimately looking for the ratio between tickets created and number of calls taken.
Answer Chosen by the Author
SELECT T.ID, T.TITLE, T.CREATED,
CREATOR.FULL_NAME as "Creator",
SUBMITTER.FULL_NAME as "Submitter"
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE TC on T.ID = TC.HD_TICKET_ID and TC.ID = (select MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = T.ID)
JOIN USER CREATOR ON TC.USER_ID = CREATOR.ID
JOIN USER SUBMITTER on T.SUBMITTER_ID = SUBMITTER.ID
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 YEAR)