/build/static/layout/Breadcrumb_cap_w.png

Customize Report for Service Desk

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

Answers (2)

Posted by: dchristian 12 years ago
Red Belt
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
Posted by: Roger Matias 10 years ago
White Belt
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

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ