Click here to Skip to main content
14,739,223 members
Please Sign up or sign in to vote.
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:

StoreScoreRankGradeWeighted Grade

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.

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

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' 
from StoreRanking

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

Hope it helps.
Mehdi Gholam 27-Dec-11 0:29am
Amir Mahfoozi 27-Dec-11 0:30am
Thanks Mehdi :)
Use the following system :
A..F = 6 places
Range = Highest-Lowest

Rank = ((Score-Lowest)/Range ) *6
Amir Mahfoozi 27-Dec-11 0:32am
+5 yes the concept is as you mentioned.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900