Satisfaction Survey Average Rating Report
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!
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
Answers (2)
Please log in to answer
Posted by:
DContreras
14 years ago
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)
Average Satisfaction Rating by Technician (closed tickets last 45 days)
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
Posted by:
bleucube
14 years ago
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
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.