/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


I need help creating a SQL query to show daily active tickets

07/08/2015 1369 views
I need help creating a SQL query to show daily active tickets i.e. tickets that are not in a closed state. SO far I have 

SELECT COUNT(*) FROM ORG1.HD_TICKET WHERE HD_QUEUE_ID=1 AND HD_STATUS_ID!=2 AND HD_STATUS_ID!=58 AND HD_STATUS_ID!=57;

Which shows active tickets but only for the day that the query is ran. Is it possible to have show the past few days? An example would be Monday there was 29, Tuesday there was 27, etc. 

0 Comments   [ + ] Show comments

Comments


All Answers

0
I think you have to use the TIME_CLOSED date field also.

SELECT COUNT(*) FROM ORG1.HD_TICKET
  WHERE HD_QUEUE_ID=7 
    AND HD_STATUS_ID!=2 AND HD_STATUS_ID!=58 AND HD_STATUS_ID!=57
    AND TIME_CLOSED >= DATE_ADD(NOW(), INTERVAL -7 day)
Please change the interval as you need it.
Answered 07/09/2015 by: aragorn.2003
Red Belt

  • It gave me a response of "0" which isn't correct. Did I do something wrong?
    • Sorry, i have to change the QUEUE_ID to 7, cause this is our default queue. you have to change it back to "1" as you can see in your original post.
      • I changed the QUEUE_ID to 1 and I'm still getting the same number.
      • Also, I'm trying to get it in a form like this: 7/5/15: 28 active, 7/6/15: 27 active, 7/7/15: 28 active, etc. Does that make sense?

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