Here's my starting code, but the custom_field_value6 is not populating the report and they want it sorted and numbered by the custom field, rather than owner.

select HD_TICKET.CUSTOM_FIELD_VALUE6 as DEPARTMENT,
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 CUSTOM_FIELD_VALUE6, OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Can anyone give me some insite as to what I am missing? I've looked at it until I am cross-eyed. I don't even need any detail. Just a count of how many tickets each department submitted. Department is the CUSTOM_FIELD_VALUE6.

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
select HD_TICKET.CUSTOM_FIELD_VALUE6 as DEPARTMENT,

Without any testing of any kind, have you tried changing the fist line to something like this:
select COUNT(HD_TICKET.CUSTOM_FIELD_VALUE6) as DEPARTMENT_COUNT,
Answered 02/20/2012 by: scottlutz
Orange Senior Belt

Please log in to comment
0
Tried that, but then I only got one ticket, no department listed and that was it.
Answered 02/20/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
0
Okay, the code below gives me what I am looking for by Submitter, I also have it doing the Break on Column on the SUBMITTER_NAME. This give me the count of how many tickets a particular person has put in. Now if I can get the same thing by department, it would make my headache go away.

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
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_OPEN,
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_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by SUBMITTER_NAME

How do I get the CUSTOM_FIELD_VALUE6 = DEPARTMENT to work in here? I thought I had it, but it doesn't put the department names into the report. ANY help would be gratefully accepted.

D
Answered 02/20/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
0
Where exactly are you pulling department from?
Answered 02/21/2012 by: dchristian
Red Belt

Please log in to comment
0
I created a list in Custom Field 6 with all the departments that are covered by our help desk. We are a county government help desk, so the departments are anything from administration to sheriff. I did the same thing with different types of categories for problems, like Software::JDE or Hardware:: PC Setup. I haven't had any problem getting that to show in custom reports, but the departments just show up blank. I get the heading, but no departments listed.

Hope that helps.

D
Answered 02/21/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
0
What happens when you run this?

SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
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_OPEN,
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,
HD_TICKET.CUSTOM_FIELD_VALUE6 AS DEPARTMENT
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 31 DAY)
ORDER BY SUBMITTER_NAME
Answered 02/21/2012 by: dchristian
Red Belt

Please log in to comment
0
Still getting nothing under the Department column.[:(]
Answered 02/21/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
2
Are you looking for Custom 6 from the Database or the GUI?

Custom 6 in the GUI is really custom 5 in the DB.

Try this:
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
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_OPEN,
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,
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DEPARTMENT
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 31 DAY)
ORDER BY SUBMITTER_NAME
Answered 02/21/2012 by: dchristian
Red Belt

Please log in to comment
0
EUREKA!!![:D]

Now how do I get it to give me a sort and count by department?

I told it to Break on Column CUSTOM_FIELD_VALUE5, but it isn't doing it.
Answered 02/21/2012 by: DragonCrone
Orange Senior Belt

Please log in to comment
0
Change the order by submitter to order by the custom field.

The count is a little more tricky because you have to choose some fields to get rid of when you aggregate the totals.

Give the SQL a try, and post your results.

We'll help if you get stuck.
Answered 02/21/2012 by: dchristian
Red Belt

Please log in to comment
0
Here is the final result:

SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
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_OPEN,
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,
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DEPARTMENT
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 31 DAY)
ORDER BY DEPARTMENT, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Break on Column: DEPARTMENT

Gives me a count on each department! When I had CUSTOM_FIELD_VALUE5 in the Break on Column, it didn't break, but when I changed it to DEPARTMENT, it worked!

Thanks to all of you for all your help. I'm learning more SQL everyday. Hopefully, I'll be able to help some rusty or new users soon.

D
Answered 02/21/2012 by: DragonCrone
Orange Senior Belt

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