Hi Community,

I am trying to run the following query against the KACE database and it success the problem is the data changes day by day but they shouldn't. What I mean is, if I run this query on the 1st of September and the 2nd of September the count tickets opened is different on those two dates, the same goes for the tickets closed. Any idea what the reason may be?


set @start_date := '2015-08-01 00:00:00';
set @end_date := '2015-08-31 23:59:59';
set @cat := '1,3,4,9'; /*queues ids from HD_QUEUE table*/
set @state := '2,7,11,15,19,23,27,31,35,39'; /*the closed status ids from hd_status_table*/
set @owners := '1218,1219,1220,1221,1222,1224,1725'; /*technicians ids from user table*/
set @countries := 'UK,USA,France,Germany,China,Malaysia,Mexico';
set @UK:= 'UK';
set @USA:= 'USA';
set @France:= 'France';
set @Germany:= 'Germany';
set @China:= 'China';
set @Malaysia:= 'Malaysia';
set @Mexico:= 'Mexico';
/*the closed tickets should be filtered by their closed date, by their queue
by technician and if it is closed or not, we don't care about when the ticket was opened*/

SELECT SUM(tickets) as totaltickets,'Closed Tickets' as type from /*creating a virtual column called type*/
(
/* count the tickets closed present at the live table*/
SELECT COUNT(HD_TICKET.ID) as tickets FROM HD_TICKET
WHERE (HD_TICKET.TIME_CLOSED between @start_date and @end_date) /*set closed date filter*/
AND FIND_IN_SET(HD_TICKET.HD_QUEUE_ID, @cat) /*set queue filter*//*set technicians filter */
AND (FIND_IN_SET(HD_TICKET.HD_STATUS_ID, @state)) /*set the status filter (closed or not) */
AND FIND_IN_SET(HD_TICKET.CUSTOM_FIELD_VALUE0, @UK)
)a
UNION
/*opened tickets should be filtered only by their opened date and the queue*/
SELECT SUM(tickets) as totaltickets,'Created Tickets' as type from
(
SELECT COUNT(HD_TICKET.ID) as tickets FROM HD_TICKET
WHERE (HD_TICKET.CREATED between @start_date and @end_date) /*filter date opened*/
AND FIND_IN_SET(HD_TICKET.HD_QUEUE_ID,@cat) /*filter queues*/
AND FIND_IN_SET(HD_TICKET.CUSTOM_FIELD_VALUE0, @UK)
)b
UNION
(
  SELECT ticketsopen-ticketsclosed as totaltickets, 'Left Open' as type from /* select columns from subqueries in order to do the math, 
  negative values means that we closed more than were created during the month (opened previous months but closed this month)*/
  (
   (
    SELECT COUNT(HD_TICKET.ID) as ticketsclosed FROM HD_TICKET
    WHERE (HD_TICKET.TIME_CLOSED between @start_date and @end_date) /*set closed date filter*/
    AND FIND_IN_SET(HD_TICKET.HD_QUEUE_ID, @cat) /*set queue filter*/
    AND (FIND_IN_SET(HD_TICKET.HD_STATUS_ID, @state)) /*set the status filter (closed or not) */
    AND FIND_IN_SET(HD_TICKET.CUSTOM_FIELD_VALUE0, @UK)
   ) as f
  ,
   (
    SELECT COUNT(HD_TICKET.ID) as ticketsopen FROM HD_TICKET
    WHERE (HD_TICKET.CREATED between @start_date and @end_date) /*filter date opened*/
    AND FIND_IN_SET(HD_TICKET.HD_QUEUE_ID,@cat) /*filter queues*/
    AND FIND_IN_SET(HD_TICKET.CUSTOM_FIELD_VALUE0, @UK)
   ) as g
  )
)




1 Comment   [ + ] Show Comment

Comments

  • is your requirement is to view Open Ticket and Closed ticket in current month? and you want to see data datewise?
    • How else am I supposed to find tickets closed and tickets opened in date range?
Please log in to comment

Answers

2
This is the report that I use for total tickets opened and closed per month. It does not limit to a specific month:
SELECT YEAR(CREATED) AS TicketYears, MONTH(CREATED) AS TicketMonths, COUNT(ID) AS "Opened",
(SELECT COUNT(ID) FROM HD_TICKET WHERE YEAR(TIME_CLOSED) = TicketYears and MONTH(TIME_CLOSED) = TicketMonths) AS "Closed"
FROM ORG1.HD_TICKET
GROUP BY YEAR(CREATED), MONTH(CREATED)

Answered 09/03/2015 by: chucksteel
Red Belt

  • This is a very nice and neat query.
    I have some questions about it.

    Does it resolve the problem I mentioned on my first post, and why does it happen?

    What the difference between CREATED, TIME_OPENED, TIME_CLOSED columns in the ORG1.HD_TICKET table?
    • No, I don't believe that it resolves your problem of why the query returns different results based on the day that you run it. I'm not sure why that is happening.

      CREATED: time ticket was created
      TIME_OPENED: I believe this column relates to ticket states and when a ticket is moved into an "open" state according to the ticket status, but I'm not positive.
      TIME_CLOSED: the time the ticket was placed into a "closed" state.
      • Thanks for replying and providing the query you were very helpful.
Please log in to comment
Answer this question or Comment on this question for clarity