Solved

Weighted Averages

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

911 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

23 Experts available now in Live!

Get 1:1 Help Now