SQL帮助等级量表的等级，最低等级始终为F，最高等级始终为A [英] SQL help grade scale with rank, lowest rank will be F and highest rank will be an A always

问题描述

我正在为本地公司构建自定义报告解决方案.他们有将按季度评分的商店.我拥有的数据类型是:

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.

推荐答案

这里是解决方案，我认为您的表名称为StoreRanking

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.

使用以下系统:

Use the following system :

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

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

这篇关于SQL帮助等级量表的等级，最低等级始终为F，最高等级始终为A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！