Hello,

I am trying to learn how to make theses custom reports to fit our company needs, I have been able to modify a few reports, the coding below runs a report I modified but it only seems to show the closed tickets. I need it to show closed, opened and or stalled.

Is this possible?

select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       DATE_FORMAT(HD_TICKET.TIME_OPENED, '%m-%d-%y %H:%i') as TIME_OPENED,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d-%y %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_PRIORITY.NAME as PRIORITY,
       HD_CATEGORY.NAME as CATEGORY,
       HD_STATUS.NAME as STATUS,
       HD_IMPACT.NAME as IMPACT,
       HD_TICKET.SATISFACTION_RATING as SATISFACTION_RATING,
       HD_TICKET.SATISFACTION_COMMENT,
       HD_TICKET.MODIFIED,
       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 = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 63 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

 

Thank you for your help.

Answer Summary:
where ((HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 63 DAY) OR (HD_STATUS.STATE = 'stalled') OR (HD_STATUS.STATE = 'open'))
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

4

I don't use the service desk w/ Kace so I can't test this, but take a look at this line.

Change 

where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 63 DAY)

to....

where HD_STATUS.STATE = 'closed' OR HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'open'
Answered 08/27/2012 by: dugullett
Red Belt

  • If you do use

    where ((HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 63 DAY) OR (HD_STATUS.STATE = 'stalled') OR (HD_STATUS.STATE = 'open'))

    is should still return tickets closed in the past 63 days along with all open and stalled.
Please log in to comment
Answer this question or Comment on this question for clarity