Solved

Weighted Averages

Posted on 2013-05-26
5
225 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
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now