SQL help grade scale with rank, lowest rank will be F and highest rank will be an A always

Question

5.00/5 (1 vote)

See more:

I am building a custom reporting solution for a local company. They have Stores which will be graded on a quarterly basis. The type of Data I have is:

Store	Score	Rank	Grade	Weighted Grade
1	98	1	A	A
7	96	2	A	?
5	95	3	A	?
4	92	4	A-	?
3	89	5	B+	?
2	83	6	B	?
6	80	7	B-	F

I originally created the report using the standard grading scale of A - F. The Client however always wants the lowest ranked store to an F and the Highest rank store to always be an A (even though their grade may only be an 89 or the lowest store's actual grade may be an 80)

I am having trouble wrapping my head around this to get the grades correct. I currently have a table that I've built with the grade letter and the low and high range of the grade and I just join against it and say where the score between the high and low and show the grade. How do I create a curve on this with SQL Server so the lowest graded store is always an F and the highest graded store is always an A.

Thanks guys.

Posted 26-Dec-11 16:01pm

r83h

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Answer 1 · 2011-12-26T18:22:00

Here is the solution, I supposed that your table name is StoreRanking

SQL

declare @max int , @min int, @diff int , @step int

select @max=MAX(score) from StoreRanking 
select @min=MIN(score) from StoreRanking 
select @diff = @max -@min , @step = @diff / 6

select *, (score-@min) , @diff , @step, 
  case when score <= (@min + @step) then 'F' 
       when score <= (@min + 2*@step) then 'E' 
       when score <= (@min + 3*@step) then 'D' 
       when score <= (@min + 4*@step) then 'C' 
       when score <= (@min + 5*@step) then 'B' 
       when score <= (@min + 6*@step) then 'A' 
  end
from StoreRanking

Change it to an update statement or use it as is.

Hope it helps.

Mehdi Gholam · Answer 2 · 2011-12-26T18:28:00

Solution 2

Use the following system :

A..F = 6 places
Range = Highest-Lowest

Rank = ((Score-Lowest)/Range ) *6

Posted 26-Dec-11 18:28pm

Mehdi Gholam

Comments

Amir Mahfoozi 27-Dec-11 0:32am

+5 yes the concept is as you mentioned.