Rank Account Transaction by Year and Week

[JustATheory]

Greetings,

I have a table that lists Account and Date of Transaction, and I need to add two columns YearRank that lists sequentially each time a transaction occurs by account by year, and then it's rank within a week’s timeframe. I did this years ago in Access and can’t find the code, as I recall it was using the count(*) in a subquery, but I’m not getting the expected results. Any help is greatly appreciated.

Thanks,
JustATheory

Existing Need to add Need to add
Account Date YearRank WeeklyRank
123 1/3/2010 1 1
246 1/3/2010 1 1
123 2/1/2010 2 1
246 1/15/2010 2 1
246 1/16/2010 3 2

 

[markros]

In SQL Server 2005 and up take a look at new windowing functions such as RANK() and DENSE_RANK()

PluralSight Learning Library