SQL Scripting Help
Hi Guys,
I have been attempting to write a script to give me ticket stats for the past 12 months from K1000. All I am looking for is a Count of tickets by month for the past 12 months. Because we use archiving, I am having to join the results of two queries. I have attempted to use a UNION, which works but I get some months repeated because of aged open tickets. Here is the script I've attempted to use:-
SELECT COUNT(H.ID) as total_opened,
HD_STATUS.NAME as status,
MONTH(H.CREATED) as month,
YEAR(H.CREATED) as year
FROM HD_TICKET H
JOIN HD_STATUS ON (HD_STATUS.ID=H.HD_STATUS_ID)
WHERE (H.HD_QUEUE_ID > 0)
AND (HD_STATUS.NAME LIKE '%closed%')
AND H.CREATED >= DATE_SUB(DATE_ADD(last_day(NOW()), INTERVAL 1 DAY), INTERVAL 1 YEAR)
GROUP BY month
UNION
SELECT COUNT(A.ID) as total_opened,
HD_STATUS.NAME as status,
MONTH(A.CREATED) as month,
YEAR(A.CREATED) as year
FROM HD_ARCHIVE_TICKET A
JOIN HD_STATUS ON (HD_STATUS.ID=A.HD_STATUS_ID)
WHERE (A.HD_QUEUE_ID > 0)
AND (HD_STATUS.NAME LIKE '%closed%')
AND A.CREATED >= DATE_SUB(DATE_ADD(last_day(NOW()), INTERVAL 1 DAY), INTERVAL 1 YEAR)
GROUP BY month
ORDER BY year, month
Any advice?
2 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
I would also recommend changing your HD_STATUS.NAME like '%closed%' to HD_STATUS.STATE = "closed"
This will make sure that if you have any status names that don't include the word "closed" will match (some of our closed states use the word finished, done, or completed, etc.). - chucksteel 6 years ago
That's improved the results. You are correct only closed tickets get archived, however we sometimes have tickets that can stay open for over a year. These are our aged tickets. We have Kace set to archive closed tickets after 3 months, but some tickets that were created over the 3 months but then closed don't seem to get archived. - Druis 6 years ago