Hello Everybody,

I had a request to exclude the status of "Project" on tickets for a paticular department (Queue 8). I'm not familar with how to exclude anything. Could someone please point me in the right direction on how to do this with the following report?

SELECT
(case
when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 1 HOUR) then '0-1 hour'
when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'
when TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'
else 'error' end ) as CLOSE_GROUP,
count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) div 86400),'d ', TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET, HD_STATUS
where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
group by CLOSE_GROUP

UNION
select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET
,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) 
and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
group by CLOSE_GROUP

Thanks,

 

Anthony

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

Try adding this line after "and HD_STATUS.NAME='closed' :

and HD_STATUS.NAME<>'project'

John

Answered 02/18/2013 by: jverbosk
Red Belt

  • Thanks for that John, I made the change and I'm comparing to verify this works for them.



    Anthony
  • Hey John,

    As it turns out, it isn't the status which is set to project however instead it is priority. I attempted to change HD_STATUS.NAME<>'project' to HD_PRIORITY.NAME<>'project' but this didn't seem to pass the KACE smell test and errored out. Maybe you could help me with this?

    Thanks,

    Anthony
  • The reason it doesn't work is because the HD_PRIORITY table isn't specified in the query. I added it in along with what you specified, so try this and see where it gets you.

    John
    ___________________

    SELECT
    (case
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 1 HOUR) then '0-1 hour'
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'
    when TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'
    else 'error' end ) as CLOSE_GROUP,
    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) div 86400),'d ', TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET, HD_STATUS
    where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
    from HD_TICKET
    join HD_STATUS on (HD_TICKET.HD_STATUS=HD_STATUS.ID)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    • Hi John, When I attempt to enter what you provided I am getting the error: "mysql error: [1054: Unknown column 'HD_TICKET.HD_STATUS' in 'on clause'] in EXECUTE(". When I look through the code I can't seem to find what is causing this..

      Anthony
  • The error was due to a typo - should have been HD_TICKET.HD_STATUS_ID in the JOIN statement. Try this.

    John
    _______________________

    SELECT
    (case
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 1 HOUR) then '0-1 hour'
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'
    when TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'
    else 'error' end ) as CLOSE_GROUP,
    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) div 86400),'d ', TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET, HD_STATUS
    where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
    from HD_TICKET
    join HD_STATUS on (HD_STATUS.ID=HD_TICKET.HD_STATUS_ID)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    • John, it seems I am getting this same error, but this time with the Queue: "mysql error: [1054: Unknown column 'HD_TICKET.HD_QUEUE' in 'where clause'] in EXECUTE("
  • OK, I changed the top part (above UNION) to reflect the same syntax, joins and where filters as the lower part, see if this gets it.

    John
    ____________________

    SELECT
    (case
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 1 HOUR) then '0-1 hour'
    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'
    when TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'
    else 'error' end ) as CLOSE_GROUP,
    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) div 86400),'d ', TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
    from HD_TICKET
    join HD_STATUS on (HD_STATUS.ID=HD_TICKET.HD_STATUS_ID)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
    from HD_TICKET
    join HD_STATUS on (HD_STATUS.ID=HD_TICKET.HD_STATUS_ID)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
    group by CLOSE_GROUP
  • Thanks for that John, I'll send that over and see if this is better for them.
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity