Solved

Weighted Averages

Posted on 2013-05-26
5
228 Views
Last Modified: 2013-05-26
Hi,

I have a customer survey that asks four questions regarding a helpdesk incident.

How would you rate the waiting time
How would you rate the resolution time
Was the helpdesk agent easy to understand
How do you rate the competency of the helpdesk analyst.

The customer then has the following choices per question

Excellent
Very Good
Satisfactory
Very Bad
Unacceptable

When the answers are submitted, data is inserted in to the following fields in my database.

ID - A unique ID for the survey respondent
YEARMON - the year and month of the respondent 2012.01
Market - Country of respondent.
Q_ID - The ID of the question listed above. I.E Q_WT corresponds to wait time...
Response - Response from the list of the above. I.E Very Bad
Customer - The Customers name

I have been asked to calculate the average score per question. With the responses below have the following scores -

Excellent 100
Very Good 75
Satisfactory 50
Very Bad 25
Unacceptable 0

I am really unsure what the calculation I need to use to work out the average per question, grouped by month and year.

PS the survey is not live and if anyone has any recommendations over my database structure then please feel free to advise.

Can anyone help me
0
Comment
Question by:mr_bungle89
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39197562
what version of sql server are you using?
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39197566
if you want the average per question irrespective of the month then use the below

select AVG(case Response 
				WHEN 'Excellent' THEN 100
				WHEN 'Very Good' THEN 75
				WHEN 'Satisfactory' THEN 50
				WHEN 'Very Bad'		THEN 25
				when 'Unacceptable' then 0
				ELSE 0
				end
			) AS AVERAGE,Q_ID
FROM <YOUR TABLE>
GROUP BY Q_ID

Open in new window


if you want the average per question and per month then you can do the below

select AVG(case Response 
				WHEN 'Excellent' THEN 100
				WHEN 'Very Good' THEN 75
				WHEN 'Satisfactory' THEN 50
				WHEN 'Very Bad'		THEN 25
				when 'Unacceptable' then 0
				ELSE 0
				end
			) AS AVERAGE.q_id,YEARMON 
FROM <YOUR TABLE>
GROUP BY Q_ID,YEARMON 

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39197570
0
 

Author Comment

by:mr_bungle89
ID: 39197623
Thanks Neo_jarvis.

I have another question, I need to plot these figures on a graph with the Y axis showing the scores with the text and the X axis showing the averages calculated in the query above.


Excellent 100
Very Good 75
Satisfactory 50
Very Bad 25
Unacceptable 0

Is there a way I can incorporate the query above? Happy to open another question but just wanted to see if it's possible. I have also attached how my graph looks now.
example.png
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39198481
I would suggest you do not store the string responses, instead store values associated with those strings. Then "lookup" the strings if you need to. This enables you to alter/add questions, markets, responses over time. I also would store the date of a response, not just the year and month (gives you more options).

see: http://sqlfiddle.com/#!3/8b4f8/2

nb: This is not full normalized, I'm really just suggesting use of lookups

e.g.
CREATE TABLE Lookups
	([id] int, [lookup] varchar(6), [lkp_enum] int, [lkp_code] varchar(4), [label] varchar(55))
;
	
INSERT INTO Lookups
	([id], [lookup], [lkp_enum], [lkp_code], [label])
VALUES
	(1, 'rating', 0, NULL, 'Excellent'),
	(2, 'rating', 25, NULL, 'Very Good'),
	(3, 'rating', 50, NULL, 'Satisfactory'),
	(4, 'rating', 75, NULL, 'Very Bad'),
	(5, 'rating', 100, NULL, 'Unacceptable'),
	(6, 'quest', NULL, 'Q_WT', 'How would you rate the waiting time'),
	(7, 'quest', NULL, 'Q_RT', 'How would you rate the resolution time'),
	(8, 'quest', NULL, 'Q_EU', 'Was the helpdesk agent easy to understand'),
	(9, 'quest', NULL, 'Q_CA', 'How do you rate the competency of the helpdesk analyst.'),
	(10, 'market', NULL, 'UK', 'U.K.'),
	(11, 'market', NULL, 'USA', 'USA'),
	(12, 'market', NULL, 'EURO', 'Europe'),
	(13, 'market', NULL, 'ASIA', 'Asia')
;

CREATE TABLE Responses
	([ID] int, [DateCreated] datetime, [Market] varchar(3), [Q_ID] varchar(4), [Response] int, [Customer] varchar(5))
;
	
INSERT INTO Responses
	([ID], [DateCreated], [Market], [Q_ID], [Response], [Customer])
VALUES
	(1, '2013-01-01', 'UK', 'Q_WT', 0, 'Fred'),
	(2, '2013-01-01', 'UK', 'Q_RT', 0, 'Fred'),
	(3, '2013-01-01', 'UK', 'Q_EU', 0, 'Fred'),
	(4, '2013-01-01', 'UK', 'Q_CA', 0, 'Fred'),
	(5, '2013-01-11', 'USA', 'Q_WT', 100, 'Wilma'),
	(6, '2013-01-11', 'USA', 'Q_RT', 100, 'Wilma'),
	(7, '2013-01-11', 'USA', 'Q_EU', 100, 'Wilma'),
	(8, '2013-01-11', 'USA', 'Q_CA', 100, 'Wilma')
;

select
  r.id
, r.DateCreated
, r.market
, lm.label Region
, r.q_id
, r.response
, r.customer
, lq.label as question
, lr.label as choice
from responses as r
inner join lookups as lq on lq.lookup='quest' and r.q_id=lq.lkp_code
inner join lookups as lr on lr.lookup='rating' and r.response=lr.lkp_enum
inner join lookups as lm on lm.lookup='market' and r.market=lm.lkp_code
;

select
  r.market
, lm.label Region
, r.response
, lr.label as choice
, sum(r.response) / count(r.response) av_resp
from responses as r
inner join lookups as lr on lr.lookup='rating' and r.response=lr.lkp_enum
inner join lookups as lm on lm.lookup='market' and r.market=lm.lkp_code
group by
  r.market
, lm.label
, r.response
, lr.label
;

select
  lq.label as question
, sum(r.response) / count(r.response) av_resp
from responses as r
inner join lookups as lr on lr.lookup='rating' and r.response=lr.lkp_enum
inner join lookups as lq on lq.lookup='quest' and r.q_id=lq.lkp_code
group by
  lq.label
;

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question