I need help creating a SQL query to show daily active tickets
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
Answers (1)
Please log in to answer
Posted by:
aragorn.2003
8 years ago
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.
Comments:
-
It gave me a response of "0" which isn't correct. Did I do something wrong? - ctw195 8 years ago
-
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. - aragorn.2003 8 years ago
