I would like a report that will (or come close too) displaying the average satisfaction survey rating for x number of days.

Let say I want to know in the last 45 days what average rating does helpdesk have based on all closed tickets.

The sql statement would need to find all closed tickets that have statisfaction survey's attached, add up all the ratings and divide by the number of tickets found for x number of days needed.

I've tried to hack the default "Closed satisfaction survey last 31 days by owner) but I'm not getting anywhere.

Any help appreciated!
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
I have were it will list all satisfaction numbers for a particular year:

SELECT HD_TICKET.SATISFACTION_RATING AS HD_TICKET_SATISFACTION_RATING, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_TICKET.SATISFACTION_RATING NOT LIKE '%0%' AND DATE(HD_TICKET.TIME_CLOSED) LIKE '%2009%') ORDER BY HD_TICKET.TIME_CLOSED asc,HD_TICKET.SATISFACTION_RATING asc
Answered 12/02/2009 by: bleucube
Senior Yellow Belt

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

2
I put these together very quickly so let me know it they are working for you.

Average Satisfaction Rating by Category (closed tickets last 45 days)

SELECT AVG(HD_TICKET.SATISFACTION_RATING) AS 'Average Satisfaction Rating', HD_CATEGORY.NAME
as 'Category'
FROM HD_TICKET
JOIN HD_CATEGORY on HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
JOIN HD_STATUS ON HD_TICKET.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 HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 45 DAY)
GROUP BY HD_CATEGORY.NAME
ORDER BY Category asc


Average Satisfaction Rating by Technician (closed tickets last 45 days)

SELECT AVG(HD_TICKET.SATISFACTION_RATING) as 'Average Satisfaction Rating', ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as 'Technician'
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 HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 45 DAY)
GROUP BY Technician
ORDER BY Technician asc
Answered 12/02/2009 by: DContreras
Orange Belt

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