Ranking

[thembela]

I’m trying to rank the brokers for this month(June) and the previous month(May) and then I have to find out for how many times has the broker been a number 1, 2 ,3 or 4 up 10 in a year : Here’s how it should look like

Broker Pos(June) Prev(May) HowManytimes in a YR
Beverly 1 1 11
Shana 2 6 8
Benita 3 10 6
Sarita 4 7 2

Please help.

 

[PHV]

Any chance you could post the structure of the brokers table and what you have tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thembela]

Underneath are the results then followed by the sql statement.I want it to give me first Ranking for this month(June) and for the last month(May) and then i need to know how many times has a broker held a certain positon in a year


EffMonth Broker APIMvmnt Ranking
01/03/2004 1003282694 263040 1
01/03/2004 1011716212 110376 2
01/05/2005 1003092416 101040 3
01/06/2005 1030915845 99408 4
01/04/2005 1004731400 95244 5
01/11/2004 1004638332 93588 6
01/05/2005 1012150114 89352 7
01/11/2004 1001571189 88812 8
01/05/2004 1002935813 87504 9
01/12/2004 1065235069 83544 10

SELECT TOP 10 Prod1.EffMonth, Prod1.Broker, Prod1.APIMvmnt, (select count([pROD1].[APImVMNT]) FROM pROD WHERE [APIMvmnt] > [Prod1].[ApiMvmnt] -1) AS Ranking
FROM Prod AS Prod1
GROUP BY Prod1.EffMonth, Prod1.Broker, Prod1.APIMvmnt
ORDER BY Prod1.APIMvmnt DESC;

 

[PHV]

A starting point:
SELECT A.EffMonth, A.Broker, Count(*) As Ranking
FROM Prod AS A INNER JOIN Prod AS B
ON A.EffMonth = B.EffMonth AND A.APIMvmnt <= B.APIMvmnt
GROUP BY A.EffMonth, A.Broker
HAVING Count(*) <= 10;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thembela]

It works perfectly fine but now, i need to do the ranking for previous month(May),in a diffent column.So May is gonna be Rank 2