Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Question Answered: How to take grade letter from marks range from grade table??

    Grade Table is:

    Grade_letter lowbound_marks uperbound_marks
    A 85 100
    B 60 84
    C 0 59

    Suppose some another query returns marks.
    Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hardcoded and calculates the grade based on the values given in the table only.

  2. Best Answer
    Posted by Pat Phelan

    "
    Quote Originally Posted by vivekpatel1234 View Post
    Grade Table is:

    Grade_letter lowbound_marks uperbound_marks
    A 85 100
    B 60 84
    C 0 59

    Suppose some another query returns marks.
    Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hardcoded and calculates the grade based on the values given in the table only.
    Your question reads like you've rephrased a classroom assignment. If that is the case, then you'd be better off in many ways if you actually did the work yourself.

    Here's one possible solution for your problem:
    Code:
    CREATE TABLE #Grades (
       Grade_letter     CHAR(1)     NOT NULL
    ,  lowbound_marks   TINYINT     NOT NULL
    ,  uperbound_marks  TINYINT     NOT NULL
    )
    
    INSERT INTO #Grades (
       Grade_letter, lowbound_marks, uperbound_marks
    )  VALUES ('A', 85, 100), ('B', 60, 84), ('C', 0, 59)
    
    CREATE TABLE #StudentMarks (
       Name             CHAR(20)    NOT NULL
    ,  Mark             TINYINT     NOT NULL
    )
    
    INSERT INTO #StudentMarks (
       Name, Mark
    )  VALUES ('Bob', 80), ('Carol', 90), ('Ted', 60), ('Alice', 50)
    
    SELECT Grade_letter, Mark, sm.Name
       FROM #Grades AS g
       INNER JOIN master.dbo.spt_values AS v
          ON ('P' = v.type
          AND v.number BETWEEN g.lowbound_marks AND g.uperbound_marks)
       INNER JOIN #StudentMarks AS sm
          ON (sm.Mark = v.number)
       ORDER BY sm.Name
    
    DROP TABLE #StudentMarks
    DROP TABLE #Grades
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by vivekpatel1234 View Post
    Grade Table is:

    Grade_letter lowbound_marks uperbound_marks
    A 85 100
    B 60 84
    C 0 59

    Suppose some another query returns marks.
    Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hardcoded and calculates the grade based on the values given in the table only.
    Your question reads like you've rephrased a classroom assignment. If that is the case, then you'd be better off in many ways if you actually did the work yourself.

    Here's one possible solution for your problem:
    Code:
    CREATE TABLE #Grades (
       Grade_letter     CHAR(1)     NOT NULL
    ,  lowbound_marks   TINYINT     NOT NULL
    ,  uperbound_marks  TINYINT     NOT NULL
    )
    
    INSERT INTO #Grades (
       Grade_letter, lowbound_marks, uperbound_marks
    )  VALUES ('A', 85, 100), ('B', 60, 84), ('C', 0, 59)
    
    CREATE TABLE #StudentMarks (
       Name             CHAR(20)    NOT NULL
    ,  Mark             TINYINT     NOT NULL
    )
    
    INSERT INTO #StudentMarks (
       Name, Mark
    )  VALUES ('Bob', 80), ('Carol', 90), ('Ted', 60), ('Alice', 50)
    
    SELECT Grade_letter, Mark, sm.Name
       FROM #Grades AS g
       INNER JOIN master.dbo.spt_values AS v
          ON ('P' = v.type
          AND v.number BETWEEN g.lowbound_marks AND g.uperbound_marks)
       INNER JOIN #StudentMarks AS sm
          ON (sm.Mark = v.number)
       ORDER BY sm.Name
    
    DROP TABLE #StudentMarks
    DROP TABLE #Grades
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Psst, Pat... you don't need the join to spt_values
    George
    Home | Blog

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    Psst, Pat... you don't need the join to spt_values
    I could use a CTE to create a numbers table to get the same result that I'm getting via spt_values, but that would be less obvious.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Oct 2015
    Posts
    2

    Cool IT worked

    Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •