14,739,223 members
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

## Solution 1

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 :)

## Solution 2

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)

Top Experts
Last 24hrsThis month
 OriginalGriff 260 Christian Graus 135 Richard Deeming 70 Member 9374423 50 pythonHumanBot 50
 OriginalGriff 3,840 Richard MacCutchan 1,798 CPallini 1,678 Richard Deeming 1,110 Maciej Los 962

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