SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Looking for help with ranking, ratings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bugulgad
Starting Member

21 Posts

Posted - 09/27/2013 :  12:41:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3328 Posts

Posted - 09/27/2013 :  12:51:53  Show Profile  Reply with Quote
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 - 09/27/2013 :  14:43:14  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/28/2013 :  03:22:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000