/build/static/layout/Breadcrumb_cap_w.png

Help refining SQL reporting Query

I took one of the generic reports from kace (opened stalled tickets by category) and customized for my needs however; i'm having trouble refining it. I would like A. to have this query generate a report so that it doesn't create one report for all the queues but only shows data for one queue. right now Helpdesk, HR, finance ....etc....they all show up in one excel sheet in queue order. How can I make this report just shows just Helpdesk. B. we have "new" and "waiting on IT status for tickets under open and  "Waiting on User" & "resolved" for our stalled tickets. I want to make it so I only see open tickets or "new" tickets when I run this report. Any help would be great I am pretty new to SQL and thought it would be better to ask the community than run around. query is below 

select 
  HD_CATEGORY.NAME as CATEGORY, 
       HD_QUEUE.NAME, 
  HD_TICKET.ID, 
       HD_TICKET.TITLE,
       date_format(HD_TICKET.MODIFIED,'%m/%d') as Updated,
       date_format(HD_TICKET.CREATED, '%m/%d') as CREATION,
       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_STATUS.NAME as STATUS,        
       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_QUEUE on HD_QUEUE_ID = HD_QUEUE.ID
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
where HD_STATUS.STATE = 'opened' OR HD_STATUS.STATE = 'stalled'
order by HD_QUEUE.NAME, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, OWNER_NAME

0 Comments   [ + ] Show comments

Answers (1)

Posted by: Druis 7 years ago
Third Degree Green Belt
0

For part A, if you add (HD_TICKET.HD_QUEUE_ID = 1) to your WHERE clause your report should limit to the specific Queue. You'll need to know the queue IDs for your different Queues.

For part B. Add (HD_STATUS.NAME like 'new') AND (HD_STATUS.NAME like 'waiting%') to the WHERE clause should limit the view to new and waiting tickets.

Don't be a Stranger!

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

Sign up! or login

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