How would I modify the following SQL query to run against a specific queue if I have one called Network/Phone??
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
You can add the queue name:
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF (Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY. NAME AS PRIORITY,
HD_CATEGORY. NAME AS CATEGORY,
HD_STATUS. NAME AS STATUS,
HD_IMPACT. NAME AS IMPACT,
MACHINE. NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER .ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER .ID) AS SUBMITTER_NAME
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE ( HD_STATUS.STATE = 'stalled'
OR HD_STATUS.STATE = 'opened' )
AND HD_QUEUE.NAME = 'Network/Phone' -- Queue name here
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Answered 03/15/2012 by: dchristian
Red Belt

Please log in to comment
0

Hello Dears,

Someone help me to put department field on the query?

Select

  HD_QUEUE.NAME,

  HD_TICKET.ID,

  HD_TICKET.TITLE,

  HD_TICKET.DUE_DATE,

  Date_Format(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') As TIME_OPENED,

  Date_Format(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') As TIME_CLOSED,

  Concat(If(Time_To_Sec(HD_TICKET.TIME_CLOSED) >=

  Time_To_Sec(HD_TICKET.TIME_OPENED), To_Days(HD_TICKET.TIME_CLOSED) -

  To_Days(HD_TICKET.TIME_OPENED), To_Days(HD_TICKET.TIME_CLOSED) -

  To_Days(HD_TICKET.TIME_OPENED) - 1), 'd ',

  Date_Format(AddTime("2000-01-01 00:00:00",

  Sec_To_Time(Time_To_Sec(HD_TICKET.TIME_CLOSED) -

  Time_To_Sec(HD_TICKET.TIME_OPENED))), '%kh %im')) As TIME_TO_CLOSE,

  HD_TICKET.RESOLUTION As RESOLUTION,

  HD_PRIORITY.NAME As PRIORITY,

  HD_CATEGORY.NAME As CATEGORY,

  HD_STATUS.NAME As STATUS,

  HD_IMPACT.NAME As IMPACT,

  MACHINE.NAME As MACHINE_NAME,

  DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%M/%Y')AS MES_FECHAMENTO,

  IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < HD_PRIORITY.ESCALATION_MINUTES,'Yes','No') AS SLA,

  IfNull((Select

    USER.FULL_NAME

  From

    USER

  Where

    HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') As OWNER_NAME,

  (Select

    USER.FULL_NAME

  From

    USER

  Where

    HD_TICKET.SUBMITTER_ID = USER.ID) As SUBMITTER_NAME,

  HD_PRIORITY.ESCALATION_MINUTES

From

  HD_TICKET Left Join

  HD_CATEGORY On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Left Join

  HD_STATUS On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID Left Join

  HD_PRIORITY On HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID Left Join

  HD_IMPACT On HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID Left Join

  MACHINE On HD_TICKET.MACHINE_ID = MACHINE.ID,

  HD_QUEUE

Where

  HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID And

  HD_STATUS.STATE = 'closed'

Order By

  IfNull((Select

    USER.FULL_NAME

  From

    USER

  Where

    HD_TICKET.OWNER_ID = USER.ID), ' Unassigned'),

  HD_PRIORITY.ORDINAL,

  HD_CATEGORY.ORDINAL,

  HD_STATUS.ORDINAL,

  HD_IMPACT.ORDINAL

 

Thanks a lot

Answered 02/13/2014 by: Roger Matias
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity