Solved

query build help

Posted on 2013-05-24
8
260 Views
Last Modified: 2013-05-24
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?
0
Comment
Question by:mr_bungle89
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39195100
What's the score for "very good". You didn't give one.
0
 

Author Comment

by:mr_bungle89
ID: 39195109
sorry I made a typo it should read very good in the scores.
0
 
LVL 20

Accepted Solution

by:
dsacker earned 250 total points
ID: 39195126
Try this:
SELECT  Date,
        AVG(CASE response
            WHEN 'very bad' THEN 25
            WHEN 'satisfactory' THEN 50
            WHEN 'very good' THEN 75
            WHEN 'excellent' then 100
            ELSE 0
        END) AS response_value
FROM YourTable
GROUP BY Date

Open in new window

You can then follow suit for your other columns.
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 39195143
You've kind of hampered yourself with bad schema but...

Create a table that maps response to a numeric value then join each column against that.

CREATE TABLE ResponseValue (
   Response nvarchar(50) NOT NULL,
   Value int NOT NULL)

INSERT INTO ResponseValue (Response, Value)
VALUES ('unacceptable', 0)

...repeat inserts for each pair

SELECT myTable.Date,
   myTable.response,
   RV1.Value AS Value1,
   myTable.fix_time,
   RV2.Value AS Value2,
   myTable.competency,
   RV3.Value as Value3,
   RV1.Value + RV2.Value + RV3.Value / 3 AS AvgResponseValue
FROM myTable
LEFT OUTER JOIN ResponseValue AS RV1
   ON myTable.response = RV1.Response
LEFT OUTER JOIN ResponseValue AS RV2
   ON myTable.fix_time = RV2.response
LEFT OUTER JOIN ResponseValue AS RV3
   ON myTable.competency = RV3.response

This also assumes you have good data...what do you want to default the value to if a response is blank?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:mr_bungle89
ID: 39195149
Thanks all,

I can still redesign the schema. What would you suggest I change it to?
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39195152
Unless you really have a purpose to segregate your tables and code, the default dbo is fine.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39195187
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39196141
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2014 get SPIDs of users 6 26
How to find duplicates in SQL Server 3 22
SQL JOIN 6 35
shrink datafile Sql server 4 16
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

758 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

19 Experts available now in Live!

Get 1:1 Help Now