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

Please log in to comment

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?
Please log in to comment
Answer this question or Comment on this question for clarity

Share