I am trying to create a report that displays all items in a particular category such as "logins" I have created a sql that displays sorted by all categories, but I just need it for the one category. Any help would be much appreciated. Here is the sql I have created


select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE,
       CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED) - 1), 'd ',
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_CLOSED))),
              '%kh %im')) AS TIME_CLOSED,
       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 = 'closed'
order by HD_CATEGORY.ORDINAL, SUBMITTER_NAME, HD_PRIORITY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL, OWNER_NAME

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
  You try adding a AND statement to your WHERE clause?

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE,
       CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED) - 1), 'd ', 
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_CLOSED))),
              '%kh %im')) AS TIME_CLOSED,
       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 = 'closed' AND HD_CATEGORY.NAME LIKE '%SOFTWARE%'  <=======
order by HD_CATEGORY.ORDINAL, SUBMITTER_NAME, HD_PRIORITY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL, OWNER_NAME
Answered 04/12/2016 by: nshah
Red Belt

  • Yes I did, I did where HD_STATUS.STATE = 'closed' AND HD_CATEGORY.NAME LIKE logins' and nothing pulled
  • AND HD_CATEGORY.NAME = 'logins' *
    • MySQL is case sensitive so verify that the category name is lower case. Also, if logins is a sub category you need to include the entire category name, e.g. Network::Logins
      • Logins is not the sub category, but when I included the subcategory with logins it worked. Thanks!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share