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 


Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Chuck I tried that but there was no option to pull automated reports from the last 7 days
Please log in to comment

Answer Chosen by the Author

1
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


Answered 12/11/2015 by: chucksteel
Red Belt

  • thank you it worked!!. How can add total # of tickets created?
    • 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
Please log in to comment

Answers

1
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.
Answered 12/10/2015 by: chucksteel
Red Belt

  • Chuck I tried that but there was no option to pull automated reports from the last 7 days
Please log in to comment
Answer this question or Comment on this question for clarity

Share