mr_bungle89
asked on
query build help
Hi,
I have a survey that asks customers about the quality of service they receive from our helpdesk. I need to build a report that averages the response against the following scores -
0 unacceptable
25 very bad
50 Satisfactory
75 Good
100 Excellent
A typical row in my database will show
Date l response l fix_time l competency l
2012.01 l very good l excellent l very bad l
2012.01 l very good l satisfactory l very bad l
2012.01 l very bad l good l good l
I am struggling to work out how i can build the SQL statement to show this. Can anyone guide me?
I have a survey that asks customers about the quality of service they receive from our helpdesk. I need to build a report that averages the response against the following scores -
0 unacceptable
25 very bad
50 Satisfactory
75 Good
100 Excellent
A typical row in my database will show
Date l response l fix_time l competency l
2012.01 l very good l excellent l very bad l
2012.01 l very good l satisfactory l very bad l
2012.01 l very bad l good l good l
I am struggling to work out how i can build the SQL statement to show this. Can anyone guide me?
dsacker
What's the score for "very good". You didn't give one.
ASKER
sorry I made a typo it should read very good in the scores.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all,
I can still redesign the schema. What would you suggest I change it to?
I can still redesign the schema. What would you suggest I change it to?
Unless you really have a purpose to segregate your tables and code, the default dbo is fine.
CREATE TABLE Survey (
SurveyID IDENTITY (int, 1,1),
UserID int (this is assuming you record a user and can tie it off otherwise ignore)
SurveyDate datetime NO NULL Default = GETDATE())
CREATE TABL SurveyQuestion (
SurveyQuestion IDENTITY (int, 1,1),
Question nvarchar(max))
CREATE TABLE SurveyResponse (
SurveyResponseID IDENTITY (int, 1,1),
SurveyID int, --Foreign Key reference to Survey Table
SurveyQuestionID int, --FK reference to SurveyQuestion Table
ResponseValueID int) --FK reference to ResponseValue Table
CREATE TABLE ResponseValue (
ResponseValueID IDENTITY (int, 1,1),
Response nvarchar(50),
Value int)
Table definition is not executable verbatim but should communicate the idea i hope.
The benefits to the lookup table are that you can use it as a source in your app and not have to worry about hardcoding the values and losing compatibility with the DB. Putting the questions in a separate table allows for expandability of the questions without adding a new column to the table. This will also make analysis of the responses much more powerful. You could create metrics by question or date or whatever much easier. BTW I would also include a 'no response' option in the ResponseValue table so they can easily be excluded without skewing any averages.
Of course this could all be overkill for your application...just depends on where you see this going in the future.
SurveyID IDENTITY (int, 1,1),
UserID int (this is assuming you record a user and can tie it off otherwise ignore)
SurveyDate datetime NO NULL Default = GETDATE())
CREATE TABL SurveyQuestion (
SurveyQuestion IDENTITY (int, 1,1),
Question nvarchar(max))
CREATE TABLE SurveyResponse (
SurveyResponseID IDENTITY (int, 1,1),
SurveyID int, --Foreign Key reference to Survey Table
SurveyQuestionID int, --FK reference to SurveyQuestion Table
ResponseValueID int) --FK reference to ResponseValue Table
CREATE TABLE ResponseValue (
ResponseValueID IDENTITY (int, 1,1),
Response nvarchar(50),
Value int)
Table definition is not executable verbatim but should communicate the idea i hope.
The benefits to the lookup table are that you can use it as a source in your app and not have to worry about hardcoding the values and losing compatibility with the DB. Putting the questions in a separate table allows for expandability of the questions without adding a new column to the table. This will also make analysis of the responses much more powerful. You could create metrics by question or date or whatever much easier. BTW I would also include a 'no response' option in the ResponseValue table so they can easily be excluded without skewing any averages.
Of course this could all be overkill for your application...just depends on where you see this going in the future.
each response is presumably a choice from a drop-down hence it should not be necessary to store the nvarchar labels at all, and at the very least I would suggest you store the value, not the label.
Date l response l fix_time l competency l
2012.01 l 75 l 100 l 25 l
2012.01 l 75 l 50 l 25 l
2012.01 l 25 l 75 l 75 l
Thi information goes into a "lookup table" that you use to convert numbers to strings when you need that
0 unacceptable
25 very bad
50 Satisfactory
75 Good
100 Excellent
If you were building for a variety of surveys then I'd certainly agree with a more normalized approach as outlined above.
Date l response l fix_time l competency l
2012.01 l 75 l 100 l 25 l
2012.01 l 75 l 50 l 25 l
2012.01 l 25 l 75 l 75 l
Thi information goes into a "lookup table" that you use to convert numbers to strings when you need that
0 unacceptable
25 very bad
50 Satisfactory
75 Good
100 Excellent
If you were building for a variety of surveys then I'd certainly agree with a more normalized approach as outlined above.