SQL帮助等级量表的等级，最低等级始终为F，最高等级始终为A

问题描述

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

Store	成绩	排名	成绩	加权成绩
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

我最初是使用标准评分标准A-F创建报告的.但是，客户始终希望评级最低的商店为F，评级最高的商店始终为A(即使其评级可能仅为89或最低)商店的实际成绩可能是80)

我无法解决这个问题，无法获得正确的成绩.我目前有一张表格，该表格是用成绩字母以及该成绩的最低和最高范围构建的，我只是加入表格，说出最高和最低之间的分数并显示该成绩.如何使用SQL Server在此上创建曲线，以便评分最低的存储区始终为F，评分最高的存储区始终为A.

谢谢大家.

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.

推荐答案

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.

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

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

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