Rank within ranked results

[ggranger007]

My goal is to rank the top 20 Requesters in the current month. However, I also want to show how they ranked over the 11 months before this.

I am using the alias function under this MS support article to initially rank the top 20 Requesters:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946&Product=acc2000
However, I have racked my brain, trying to easily pull their rank from previous months. The ranking in the support article requires sorting on one field and assigns rank based on consecutive rows.

Here is an example of what I am trying to accomplish:


SQL of my current approach. Works to rank the top 20 Requesters, but fails for everything else. "Month-1" - "Month-12" are already defined in a subquery.

SELECT TOP 20 (SELECT COUNT(*) FROM [04b - Sum of Delivery to Receipt Counts] Where [Month-1] >= [Month SUM Alias].[Month-1];) AS Rank, [Month SUM Alias].Requester, IIf([Month-2] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-2] >= [Month Sum Alias].[Month-2];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-2] >= [Month Sum Alias].[Month-2];),Null) AS Ranking2, IIf([Month-3] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-3] > [Month Sum Alias].[Month-3];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-3] > [Month Sum Alias].[Month-3];),Null) AS Ranking3, IIf([Month-4] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-4] > [Month Sum Alias].[Month-4];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-4] > [Month Sum Alias].[Month-4];),Null) AS Ranking4, IIf([Month-5] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-5] > [Month Sum Alias].[Month-5];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-5] > [Month Sum Alias].[Month-5];),Null) AS Ranking5, IIf([Month-6] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-6] > [Month Sum Alias].[Month-6];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-6] > [Month Sum Alias].[Month-6];),Null) AS Ranking6, IIf([Month-7] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-7] > [Month Sum Alias].[Month-7];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-7] > [Month Sum Alias].[Month-7];),Null) AS Ranking7, IIf([Month-8] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-8] > [Month Sum Alias].[Month-8];))<=20,(Select Count(*) from [MONTH SUM] Where [Month-8] > [Month Sum Alias].[Month-8];),Null) AS Ranking8, IIf([Month-9] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-9] > [Month Sum Alias].[Month-9];)+1)<=20,(Select Count(*) from [MONTH SUM] Where [Month-9] > [Month Sum Alias].[Month-9];)+1,Null) AS Ranking9, IIf([Month-10] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-10] > [Month Sum Alias].[Month-10];)+1)<=20,(Select Count(*) from [MONTH SUM] Where [Month-10] > [Month Sum Alias].[Month-10];)+1,Null) AS Ranking10, IIf([Month-11] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-11] > [Month Sum Alias].[Month-11];)+1)<=20,(Select Count(*) from [MONTH SUM] Where [Month-11] > [Month Sum Alias].[Month-11];)+1,Null) AS Ranking11, IIf([Month-12] Is Not Null And ((Select Count(*) from [MONTH SUM] Where [Month-12] > [Month Sum Alias].[Month-12];)+1)<=20,(Select Count(*) from [MONTH SUM] Where [Month-12] > [Month Sum Alias].[Month-12];)+1,Null) AS Ranking12, [Month SUM Alias].[Month-1], [Month SUM Alias].[Month-2], [Month SUM Alias].[Month-3], [Month SUM Alias].[Month-4], [Month SUM Alias].[Month-5], [Month SUM Alias].[Month-6], [Month SUM Alias].[Month-7], [Month SUM Alias].[Month-8], [Month SUM Alias].[Month-9], [Month SUM Alias].[Month-10], [Month SUM Alias].[Month-11], [Month SUM Alias].[Month-12]
FROM [04b - Sum of Delivery to Receipt Counts] AS [Month SUM Alias]
GROUP BY [Month SUM Alias].Requester, [Month SUM Alias].[Month-1], [Month SUM Alias].[Month-2], [Month SUM Alias].[Month-3], [Month SUM Alias].[Month-4], [Month SUM Alias].[Month-5], [Month SUM Alias].[Month-6], [Month SUM Alias].[Month-7], [Month SUM Alias].[Month-8], [Month SUM Alias].[Month-9], [Month SUM Alias].[Month-10], [Month SUM Alias].[Month-11], [Month SUM Alias].[Month-12]
ORDER BY [Month SUM Alias].[Month-1] DESC;

Is there any easy way to do this and return the results in one query?

 

[pooratlas]

007:
Looking at your posted result, the table looks like it would be created most easily with a crosstab query.

First: create a Top20 Requester query. These Requesters are you criteria for the next step.

Second: create a 'Last_12_Months' query of only the Top20 Requesters. This record range should include the current ranking month's requests. In this query create a calculated field of year & month of each request eg: YYYYMM. Note that month must be two characters or else month 10 will sort before month 6. This field will be the column header in step three.

Third: create a crosstab summary query of the above.

Note that the only thing this does not accomplish is the current month's rank number. You could sort by this column for rank or go back to step one and create a intRank field to include in the second step.

 

[ggranger007]

Thanks for your help. The problem is, this would only give me the data for the last 12 months for each Requester. This would have worked better, if their average was all I needed, but I need their ranking, relative to every other Requester for the previous months. If I create a calculated field of year & month and rank it by the sort order, January would give me 1-20, February would be 21-40, etc.

If anyone has any ideas or knows of an alternative ranking method, it would be greatly appreciated.