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
1981AA
7962A?
5953A?
4924A-?
3895B+?
2836B?
6807B-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

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


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

Hope it helps.
   
Comments
Mehdi Gholam 27-Dec-11 0:29am
   
5'ed
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
   
Comments
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