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