ITNinja

Hi All,

I'm currently trying to create a couple reports and I feel that I've gotten close. Presently, I've been working on creating reports that report:

1. Average Time Tickets Open/New Status Per Owner with Overall Time Tickets Open/New Average
2. Average Customer Satisfaction Per Owner with Overall Customer Satisfaction Average
3. Tickets Closed Per Owner Per Last 31 Days with Overall Tickets Closed in Last 31 Days

Although my code is probably incredibly inefficient, wrong, or poorly done, I've still managed to complete the third report which will be a little treat at the end of my post for people to use.

1. I don't know if I'm properly calculating things in this report and I'm having trouble creating a union for the overall average time tickets are open/new status. I feel like I'm pretty far off from where I should be:

SELECT
FULL_NAME AS 'Owner',
AVG(X) AS 'Average Time Tickets Are Open/New Status'
FROM
(
SELECT
T.ID,
U.FULL_NAME,
T.CREATED,
CONCAT(DATEDIFF(NOW(), T.CREATED)) AS X
FROM
HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue')
WHERE
T.OWNER_ID <> 0
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )
AND (S.STATE = 'opened'
OR S.STATE = 'new')
) RAW
GROUP BY FULL_NAME
UNION
SELECT
'Total',
SUM(AVG(Z)) AS 'Average Time Tickets Are Open/New Status'
FROM
(
SELECT
T.ID ,
U.FULL_NAME,
T.CREATED,
CONCAT(DATEDIFF(NOW(), T.CREATED)) AS Z
FROM
HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue')
WHERE
T.OWNER_ID <> 0
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )
AND (S.STATE = 'opened'
OR S.STATE = 'new')
) RAW
GROUP BY 'Total'


2. I currently haven't begun to work on integrating an overall customer satisfaction average on this report but I believe it's at least still functional for average customer satisfaction per owner although I could be wrong:

SELECT ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID AND FULL_NAME IN ( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )), 'Unassigned') as 'Owner', AVG(HD_TICKET.SATISFACTION_RATING) as 'Average Satisfaction Rating'
FROM HD_TICKET
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND HD_STATUS.STATE = 'closed'
and HD_TICKET.SATISFACTION_RATING != 0
and ('Owner' != 'Unassigned')
GROUP BY Owner
ORDER BY Owner


3. Here is the last report which I guarantee is working correctly although it is customized specifically for our environment (uses label_id and hd_queue). If anyone notices anything wrong with it, please point it out:

SELECT
X AS 'Owner',
Y AS '# of Tickets Closed in Last 31 Days'
FROM
(SELECT ifnull(FULL_NAME, 'Unassigned') AS X, COUNT(T.ID) AS Y FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
)
GROUP BY FULL_NAME) RAW
UNION
SELECT 'Total', SUM(Z)
FROM
(SELECT ifnull(FULL_NAME, 'Unassigned') AS X, COUNT(T.ID) AS Z FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
)
GROUP BY FULL_NAME) RAW


Thanks for any help offered and any opinions.

Regards