/build/static/layout/Breadcrumb_cap_w.png

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 5 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
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.

Comments:
  • Chuck I tried that but there was no option to pull automated reports from the last 7 days - Tobi92 5 years ago
Posted by: chucksteel 5 years ago
Red Belt
1

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 5 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 5 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ