/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

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