T-SQL Create Query - Compare and Calculate

Question

I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points".

Basically I got two tables. Tables Source table: This is a table with statistical per game averages for a category (category = column).

Destination table or result set: This is a points table. If I have the highest average in a category I get maximum points which is same as number of teams. So if I have 10 teams and I have the highest average in 3PM category I get 10 points, and so on down to 1 point for the worst team in the category. Last column is a total of all points per team.

The help I need: I would like to create a query that compares every teams value in a given column/category in the source table, ranks the results and gives the right amount of points. When finished it adds up the points per team to the Total PTS table.

Appreciate it if someone would help me or show me the path to fantasy glory.

• Mark Sinkinson: One option is to look at WINDOW functions, in this case RANK() OVER()

Answer 1

Did CTE with RANK/OVER:

Rank3PM = RANK() OVER (ORDER BY [3PM per Game])

and in the select I ended with:

TotalPoints = Rank3PM + RankREB + RankAST..

Thanks.