Calculating change in leaders for baseball stats in MSSQL

Imagine I have a MSSQL 2005 table(bbstats) that updates weekly showing various cumulative categories of baseball accomplishments for a team

    week 1
Player       H  SO   HR
Sammy        7  11    2 
Ted         14   3    0 
Arthur       2  15    0
Zach         9  14    3

    week 2
Player 开发者_Python百科      H  SO   HR
Sammy       12  16    4 
Ted         21   7    1 
Arthur       3  18    0
Zach        12  18    3

I wish to highlight textually where there has been a change in leader for each category so after week 2 there would be nothing to report on hits(H); Zach has joined Arthur with most strikeouts(SO) at 18; and Sammy is new leader in homeruns(HR) with 4

So I would want to set up a process something like a) save the past data(week 1) as table bbstatsPrior, b) updates the bbstats for the new results - I do not need assistance with this c) compare between the tables for the player(s with ties) with max value for each column and spits out only where they differ d) move onto next column and repeat

In any real world example there would be significantly more columns to calculate for

Thanks

---

Responding to Brents comments, I am really after any changes in the leaders for each category So I would have something like

select top 1 with ties player 
from bbstatsPrior
order by H desc

and

select top 1 with ties player,H 
from bbstats
order by H desc

I then want to compare the player from each query (do I need to do temp tables) . If they differ I want to output the second select statement. For the H category Ted is leader `from both tables but for other categories there are changes between the weeks

I can then loop through the columns using

    select name from sys.all_columns sc
 where sc.object_id=object_id('bbstats') and name <>'player'

---

If the number of stats doesn't change often, you could easily just write a single query to get this data. Join bbStats to bbStatsPrior where bbstatsprior.week < bbstats.week and bbstats.week=@weekNumber. Then just do a simple comparison between bbstats.Hits to bbstatsPrior.Hits to get your difference.

If the stats change often, you could use dynamic SQL to do this for all columns that match a certain pattern or are in a list of columns based on sys.columns for that table?

You could add a column for each stat column to designate the leader using a correlated subquery to find the max value for that column and see if it's equal to the current record.

This might get you started, but I'd recommend posting what you currently have to achieve this and the community can help you from there.