KACE Product Support

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
  )
)