Link to home
Create AccountLog in
Avatar of mr_bungle89
mr_bungle89

asked on

Weighted Averages

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
Avatar of Surendra Nath
Surendra Nath
Flag of India image

what version of sql server are you using?
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mr_bungle89
mr_bungle89

ASKER

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
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