I have two reports that run beautifully. They both give me the Average time opened and stalled for tickets that were closed in the last 30 days. One gives it by location. It's a school district. So, I have a location field for each campus. The other gives an average by owner. I'm trying to combine them into one report that gives the campus and then average ticket time broken down by owner. I get 27 rows, the # of locations we have, with the first report, but when i try to combine them I only get 10 rows even though I know each location has tickets closed because the first report tells me that.

Here's the location report:

SELECT HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, count(HD_TICKET.ID) as NUMBER_OF_TICKETS, AVG(HD_TICKET.CUSTOM_FIELD_VALUE12) as 'Average Time Stalled', AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
where HD_STATUS.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY LOCATION

Here's the Owner report:

SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS, AVG(HD_TICKET.CUSTOM_FIELD_VALUE12) as 'Average Time Stalled', AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER

Here's my attempt to combine them:

SELECT HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS, AVG(HD_TICKET.CUSTOM_FIELD_VALUE12) as 'Average Time Stalled', AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
Order by LOCATION

Any ideas?

1 Comment   [ + ] Show Comment

Comments

  • Imland, I would like to use your "Owner Report Query", but curious what you have in the custom fields to track the time opened?
Please log in to comment

Community Chosen Answer

1

I would suggest trying one of the queries as a subquery (specifically, an inline view).  I cover how to setup and use inline views here:

http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables

John

Answered 03/22/2013 by: jverbosk
Red Belt

  • Thanks John, I have a need for this coming up very soon!
Please log in to comment

Answers

0

IF there are no duplicate ticket owners across the locations this should work.

SELECT 

HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, 

USER.USER_NAME as OWNER, 

count(HD_TICKET.ID) as NUMBER_OF_TICKETS, 

AVG(HD_TICKET.CUSTOM_FIELD_VALUE12) as 'Average Time Stalled', 

AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'

from HD_TICKET

JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)

where HD_STATUS.NAME='closed'

and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)

and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/

GROUP BY LOCATION, OWNER
Answered 03/21/2013 by: jdornan
Red Belt

  • This still only returns 10 rows, 6 technicians + 2 HelpDesk + Unassigned = 10 rows. So, it's only giving me one row for each ticket owner.
Please log in to comment
Answer this question or Comment on this question for clarity