Creating auotmated Weekly Reports from specific queue
hello,
Can someone please help me
i am trying to create a weekly report using SQL. i will like it to include Date created, Ticket ID, Category, Status, time closed, and Owner
any assistance will be much appreciated
1 Comment
[ + ] Show comment
-
Chuck I tried that but there was no option to pull automated reports from the last 7 days - Tobi92 8 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Have you tried creating the report with the wizard? You aren't asking for anything that the wizard shouldn't be able to produce. Once you have the report created you can schedule it to be emailed once a week.
Comments:
-
Chuck I tried that but there was no option to pull automated reports from the last 7 days - Tobi92 8 years ago
Posted by:
chucksteel
8 years ago
Top Answer
I forgot that the wizard doesn't allow for those types of date queries.
This query will find tickets closed in the past seven days:
SELECT T.CREATED, T.ID, T.TIME_CLOSED, CAT.NAME as Category, T.TIME_CLOSED, OWNER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_CATEGORY CAT ON CAT.ID = T.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID = T.HD_STATUS_ID
JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
WHERE TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND T.HD_QUEUE_ID = 2
Change the T.HD_QUEUE_ID = 2 to match the queue that you want to report.
For tickets opened in the past seven days:
SELECT T.CREATED, T.ID, T.TIME_CLOSED, CAT.NAME as Category, T.TIME_CLOSED, OWNER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_CATEGORY CAT ON CAT.ID = T.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID = T.HD_STATUS_ID
JOIN USER OWNER ON OWNER.ID = T.OWNER_ID
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND T.HD_QUEUE_ID = 2
Comments:
-
thank you it worked!!. How can add total # of tickets created? - Tobi92 8 years ago
-
That would be a different kind of report. This will show you the number of tickets opened in the past seven days:
SELECT Q.NAME, COUNT(T.ID) AS "Count"
FROM ORG1.HD_TICKET T
JOIN HD_QUEUE Q ON Q.ID = T.HD_QUEUE_ID
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY Q.ID - chucksteel 8 years ago
