Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Looking for help with ranking, ratings

Author  Topic 

bugulgad
Starting Member

21 Posts

Posted - 2013-09-27 : 12:41:04
Hello, I am looking for some help with my personal NFL football database. I would like to Rate each team with small integer value based on their stats.

tbl_team_stats
team char(20),
offense decimal (4,1)
defense decimal (4,1)

What I would like to do is rate each team based on their stats.

Here are the rating conditions.

rank teams in descending order based on offense.
top 5 teams award them a rating of 3
next 5 rating of 2
next 5 rating of 1
next 5 rating of 0
next 5 rating of -1
next 5 rating of -2
last 2 rating of -3

There are exactly 32 teams.

I would also like to do the same rating for defense but the sort order for ranking should be ascending.

And I would like store the sum of each individual stat group as well as the sum of them combined for a total rating of each team.

I am not looking for the work to be done for me, but I am asking for help in pointing me in the right direction of methodology and maybe some key commands for me to research.

thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 12:51:53
For offense, you can do it like shown below. For defense, add another rank column in the inner query and similar case expression in the outer query.
SELECT *,
CASE
WHEN OffenseRank <= 5 THEN 3
WHEN OffenseRank <= 10 THEN 2
WHEN OffenseRank <= 15 THEN 1
WHEN OffenseRank <= 20 THEN 0
WHEN OffenseRank <= 25 THEN -1
WHEN OffenseRank <= 30 THEN -2
ELSE -3
END AS OffensePoints
FROM
(
SELECT *, RANK() OVER (ORDER BY offense DESC) AS OffenseRank
FROM tbl_team_stats
) s
Go to Top of Page

bugulgad
Starting Member

21 Posts

Posted - 2013-09-27 : 14:43:14
thanks a lot, that worked perfect. I forgot to tell you about the season column, and how I wanted an aggregrate rating over multiple seasons and individual seasons.....but I figured that out.

I am starting to understand the rank function and have plenty of uses for it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-28 : 03:22:16
what if you've more than 35 records. the below suggestion will work for any number of records continuing ranking as per your sequence


DECLARE @StartValue int
SET @StartValue=3
SELECT *,
@StartValue + (1-OffenseRank) AS OffensePoints
FROM
(
SELECT *, RANK() OVER (ORDER BY offense DESC) AS OffenseRank
FROM tbl_team_stats
) s


Set any value of startvalue and it will continue from it as per your sequence

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -