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.
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_statsteam 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 3next 5 rating of 2next 5 rating of 1next 5 rating of 0next 5 rating of -1next 5 rating of -2last 2 rating of -3There 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 OffensePointsFROM( SELECT *, RANK() OVER (ORDER BY offense DESC) AS OffenseRank FROM tbl_team_stats) s |
|
|
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 |
|
|
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 sequenceDECLARE @StartValue intSET @StartValue=3SELECT *, @StartValue + (1-OffenseRank) AS OffensePointsFROM( 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|