Returning a calculated value in T-SQL

Question

Post reply

Returning a calculated value in T-SQL

John Kress

SSC-Addicted

Points: 400
More actions
June 9, 2005 at 7:41 am

#88652

I'm looking for the best solution for returning a "Graded" value based on the content of a field.
The table is storing integer values based on questions asked in a survey. I need to grade (score) the value using a range for each field.
Example data would be T_Assessment.Home1 having possible values of 0 to 12 or null. If the value is between 0 and 6, I want to score this as 3; between 7 and 9 gets scored as 2; between 10 and 12 gets scored as 1.
T_Assessment.Home2 has possible values of 0 and 10 and null. Scoring for this field would be 0 to 1 scored as 1; 2 scored as 2; and 3-10 scored as 3.
I am trapping the null values and do not have to deal with these in the scoring process.
What suggestions would you have for this situation in T-SQL?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Seems like the score depends on the question.

I would add another table like so :

QuestionsScoresRanges

QuestionID, ScoreFrom, ScoreTo, PointsValue

so you'd put in a few lines for each questions like

1, 0, 6, 3

1, 7, 9, 2

1, 10, 12, 1

you could then query the table like this :

Select Whatever from dbo.YourTable Y inner join QuestionsScoresRanges QQS on Y.QuestionId = QQC.QuestionID and Y.Score between QQC.ScoreFrom and QQC.ScoreTO

Note that this join would also ignore the null scores by defaults .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Not a direct solution, but I think you'll get the idea

use northwind

select

sum(case when isnull(freight,0)=0 then 1 else 0 end) as 'ohne Fracht'

, sum(case when isnull(freight,0)>0 and isnull(freight,0)<18 then 1 else 0 end) as 'unter 18'

, sum(case when isnull(freight,0)>=18 and isnull(freight,0)<24 then 1 else 0 end) as 'zwischen 18 und 24'

, sum(case when isnull(freight,0)>=24 and isnull(freight,0)<65 then 1 else 0 end) 'zwischen 24 und 65'

, sum(case when isnull(freight,0)>=65 then 1 else 0 end) 'über 65'

from

orders

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's the eas way out... But he would have to code each question manually in the query, or have a single scoring plan. Which he didn't seem to have in the question. Also I doubt he wants to manually case each question .

John Kress SSC-Addicted Points: 400 More actions · Answer 4

Thanks for your help.

I used the following method to return the score:

DECLARE @HOME1Score int
SET @HOME1Score = -1
SET @HOME1Score = 
(SELECT
   CASE
      WHEN  HOME1 < 7 THEN 3
      WHEN  HOME1 > 6 and  HOME1 < 10 THEN 2
      WHEN  HOME1 > 9 THEN 1
   END AS HOME1Score
FROM T_Client_PRO_Assessment
WHERE PRO_ID=1 AND
HOME1 IS NOT NULL)

This way I can see if the score is null by a -1 value and any other value is returning the proper score value.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Whatever works... Keep my solution in mind in case you need to have different point system for each question in the same survey (might happen sooner than you think ).

Coach James Ten Centuries Points: 1272 More actions · Answer 6

I'm the best of the of best of the best, with honors!

Nice use of case John, and might I suggest using your script as a function; then put it in any queries you make as a new field.

You can set-up new functions which keep your grading scales organized and easy to read...

Regards,

Coach James