It's been a very long time since I've had to program in SQL, so I am very rusty.

What I am trying to do is take the Closed Tickets Last 31 Days by Owner report and change it to not have the Machine ID in it, but replace that column with our Category field, which is Custom_3. The Category field built in to Kbox has been renamed to Type..

Can anyone offer me any insight on how to add in that CUSTOM_3 Field in the report?

Any help would be greatly appreciated.

D
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Can you post the SQL for your starting report here?
Answered 02/13/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Here's the start of it:

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %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 TYPE,
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_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 365 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answered 02/13/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
2
Is this what you are looking for:

select HD_TICKET.CUSTOM_FIELD_VALUE2 as TYPE,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %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 TYPE,
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_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 365 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answered 02/13/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Close enough.

Thanks so much. You just saved me weeks of trying to figure it out on my own.
Answered 02/13/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
0
Okay, now they want me to be able to change the report to show only a certain group of selections in the category (CUSTOM_3).

i.e. Website::Other
or all the ones pertaining to Website::*

HELP!!!
Answered 02/13/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
2
Okay, now they want me to be able to change the report to show only a certain group of selections in the category (CUSTOM_3).

i.e. Website::Other
or all the ones pertaining to Website::*


...
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 365 DAY)
and HD_TICKET.CUSTOM_FIELD_VALUE2 LIKE 'Website::_%'
-- alternatively
-- HAVING TYPE LIKE 'Website::_%'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


In this case, putting it in the WHERE is more efficient. Putting it in HAVING is easier to understand here.
Answered 02/13/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thank you, thank you, thank you! That is perfect!
Answered 02/13/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity