Returning a calculated value in T-SQL

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

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

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

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

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

  • 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 ).

  • 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

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

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