/build/static/layout/Breadcrumb_cap_w.png
08/01/2018 246 views
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

Comments

  • We don't archive tickets, but my understanding is that only closed tickets are archived, so I'm not sure by what you mean by "aged open tickets". You might want to change your grouping to year,month instead of just month, that will make it obvious if you are grouping multiple months in different years, you shouldn't be with this query, but just in case.

    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.).
    • Thanks Chuck,
      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.
      • This report should only show tickets that were created in the past year that have a status of "%closed%". Tickets which were opened three years ago should not appear in the count regardless of if they have been archived or not.
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.
  • SELECT DISTINCT...?

There are no answers at this time

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