Decile Ranking

  • Thread starter Thread starter slotmgr70
  • Start date Start date
S

slotmgr70

Hello,

I have a query of slot players that is sorted by their level of play -
3-months, 6-months and 12-months - in ascending order. I would like to give
them a decile ranking from 1-10 based upon their 3-month ADT (play), using
6-month and 12-month ADT as the "tie-breaker". In other words, the lowest 10%
will get a "1" ranking, followed by the 2nd 10% getting a "2" ranking, and so
on.

Here is my query:

SELECT [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
FROM [Raw Summary]
GROUP BY [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
ORDER BY [Raw Summary].[3-Mo Adj ADT], [Raw Summary].[6-Mo Adj ADT], [Raw
Summary].[12-Mo Adj ADT];


Any help is appreciated.

Thanks,
Bill
 
Untested, but if you have MS SQL Server 2005 or later:

1- define an ODBC connection to it.
2- define the following pass-through query

SELECT *,
NTILE(10) OVER (ORDER BY [3-Mo Adj ADT]) AS RankOverThreeMonth,
NTILE(10) OVER (ORDER BY [6-Mo Adj ADT]) AS RankOverSixMonth,
NTILE(10) OVER(ORDER BY [12-Mo Adj ADT]) AS RankOverTwelveMonth

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
[Raw Summary])
AS callBack



Sure, you change the path and file name (here about Northwind.mdb) for
yours.

This query uses the NTILE functionality from MS SQL Server, which does what
you want, but the extra trick is to make MS SQL Server engine to 'call back'
your database in order to get the data.

And that assumes you have MS SQL Server available to you.



Vanderghast, Access MVP
 
If you don't have access to MS SQL Server 2005 (or later), you can try:

---------------------
SELECT Player_ID,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS b WHERE b.[3-Mo Adj
ADT] >= a.[3-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver3Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS c WHERE c.[6-Mo Adj
ADT] >= a.[6-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver6Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS d WHERE d.[12-Mo Adj
ADT] >= a.[12-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver12Month

FROM [Raw Summary] AS a
-----------------------

(if my ( ) are right).

I assumed there is at least 10 records in the table.



Vanderghast, Access MVP
 
Hi Vand,

I tried the query below and it produced results, but it doesn't seem to be
ranking players according to their play level. There's no correlation between
the ranking results and the actual play levels. For example, a player with
low ADT (play) was given a rank of 1.

There are thousands of records, so should we be using 10 for the count?

Thanks,
Bill



vanderghast said:
If you don't have access to MS SQL Server 2005 (or later), you can try:

---------------------
SELECT Player_ID,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS b WHERE b.[3-Mo Adj
ADT] >= a.[3-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver3Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS c WHERE c.[6-Mo Adj
ADT] >= a.[6-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver6Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS d WHERE d.[12-Mo Adj
ADT] >= a.[12-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver12Month

FROM [Raw Summary] AS a
-----------------------

(if my ( ) are right).

I assumed there is at least 10 records in the table.



Vanderghast, Access MVP



slotmgr70 said:
Hello,

I have a query of slot players that is sorted by their level of play -
3-months, 6-months and 12-months - in ascending order. I would like to
give
them a decile ranking from 1-10 based upon their 3-month ADT (play), using
6-month and 12-month ADT as the "tie-breaker". In other words, the lowest
10%
will get a "1" ranking, followed by the 2nd 10% getting a "2" ranking, and
so
on.

Here is my query:

SELECT [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
FROM [Raw Summary]
GROUP BY [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
ORDER BY [Raw Summary].[3-Mo Adj ADT], [Raw Summary].[6-Mo Adj ADT], [Raw
Summary].[12-Mo Adj ADT];


Any help is appreciated.

Thanks,
Bill
Click to expand...
Click to expand...
 
Have you tried to change the >= to <= in order to reverse the rank?

The constant 10 is because you want per-10-tiles; it would be 100 for
per-100-tiles, or 6 for per-6-tiles ( if that makes sense).


Vanderghast, Access MVP


slotmgr70 said:
Hi Vand,

I tried the query below and it produced results, but it doesn't seem to be
ranking players according to their play level. There's no correlation
between
the ranking results and the actual play levels. For example, a player with
low ADT (play) was given a rank of 1.

There are thousands of records, so should we be using 10 for the count?

Thanks,
Bill



vanderghast said:
If you don't have access to MS SQL Server 2005 (or later), you can try:

---------------------
SELECT Player_ID,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS b WHERE b.[3-Mo Adj
ADT] >= a.[3-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver3Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS c WHERE c.[6-Mo Adj
ADT] >= a.[6-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver6Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS d WHERE d.[12-Mo Adj
ADT] >= a.[12-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver12Month

FROM [Raw Summary] AS a
-----------------------

(if my ( ) are right).

I assumed there is at least 10 records in the table.



Vanderghast, Access MVP



slotmgr70 said:
Hello,

I have a query of slot players that is sorted by their level of play -
3-months, 6-months and 12-months - in ascending order. I would like to
give
them a decile ranking from 1-10 based upon their 3-month ADT (play),
using
6-month and 12-month ADT as the "tie-breaker". In other words, the
lowest
10%
will get a "1" ranking, followed by the 2nd 10% getting a "2" ranking,
and
so
on.

Here is my query:

SELECT [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
FROM [Raw Summary]
GROUP BY [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
ORDER BY [Raw Summary].[3-Mo Adj ADT], [Raw Summary].[6-Mo Adj ADT],
[Raw
Summary].[12-Mo Adj ADT];


Any help is appreciated.

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
No, the problem is that there's no correlation at all between the player's
ADT and the decile rank they received. there seems to be no logic behind it.
its as if the code is just randomly assigning a rank to each record.

Here is the SQL statement again:
SELECT a.Player_ID, 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS b WHERE
b.[3-Mo Adj ADT] >= a.[3-Mo Adj ADT])\(SELECT COUNT(*) FROM [tbl_Final]) AS
[ADT Rank 3Month], 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS c WHERE
c.[6-Mo Adj ADT] >= a.[6-Mo Adj ADT])\(SELECT COUNT(*) FROM [tbl_Final]) AS
[ADT Rank 6Month], 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS d WHERE
d.[12-Mo Adj ADT] >= a.[12-Mo Adj ADT])\(SELECT COUNT(*) FROM [tbl_Final])
AS [ADT Rank 12Month], a.[3-Mo Adj ADT], a.[6-Mo Adj ADT], a.[12-Mo Adj ADT]
FROM tbl_Final AS a;



vanderghast said:
Have you tried to change the >= to <= in order to reverse the rank?

The constant 10 is because you want per-10-tiles; it would be 100 for
per-100-tiles, or 6 for per-6-tiles ( if that makes sense).


Vanderghast, Access MVP


slotmgr70 said:
Hi Vand,

I tried the query below and it produced results, but it doesn't seem to be
ranking players according to their play level. There's no correlation
between
the ranking results and the actual play levels. For example, a player with
low ADT (play) was given a rank of 1.

There are thousands of records, so should we be using 10 for the count?

Thanks,
Bill



vanderghast said:
If you don't have access to MS SQL Server 2005 (or later), you can try:

---------------------
SELECT Player_ID,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS b WHERE b.[3-Mo Adj
ADT] >= a.[3-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver3Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS c WHERE c.[6-Mo Adj
ADT] >= a.[6-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver6Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS d WHERE d.[12-Mo Adj
ADT] >= a.[12-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver12Month

FROM [Raw Summary] AS a
-----------------------

(if my ( ) are right).

I assumed there is at least 10 records in the table.



Vanderghast, Access MVP



Hello,

I have a query of slot players that is sorted by their level of play -
3-months, 6-months and 12-months - in ascending order. I would like to
give
them a decile ranking from 1-10 based upon their 3-month ADT (play),
using
6-month and 12-month ADT as the "tie-breaker". In other words, the
lowest
10%
will get a "1" ranking, followed by the 2nd 10% getting a "2" ranking,
and
so
on.

Here is my query:

SELECT [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
FROM [Raw Summary]
GROUP BY [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
ORDER BY [Raw Summary].[3-Mo Adj ADT], [Raw Summary].[6-Mo Adj ADT],
[Raw
Summary].[12-Mo Adj ADT];


Any help is appreciated.

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Strange. Are your ADT fields numeric or alphanumeric?

To make my test, I used a table Finals, two fields, playerID (text) and ADT
(integer) , fill the table with 26 players, "a" to "z" with adt values from
1 to 26, and the query:

SELECT a.PlayerID,
1+(SELECT 10*COUNT(*)-1 FROM finals AS b WHERE b.adt >= a.adt) \ (SELECT
COUNT(*) FROM finals)
FROM Finals as a


returns the proper result (player "a" with adt of 1, get 10, as do players
"b" and "c", then "d", "e" and "f" got 9, ... up to "y" and "z" both getting
1.

If your fields ADT are alphanumerical, the order is screw up since
alphanumerically, "1", "10", "11", "2" is ordered increasingly right (as
"a", "ab", "ac", "b" are ).



Vanderghast, Access MVP.


slotmgr70 said:
No, the problem is that there's no correlation at all between the player's
ADT and the decile rank they received. there seems to be no logic behind
it.
its as if the code is just randomly assigning a rank to each record.

Here is the SQL statement again:
SELECT a.Player_ID, 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS b WHERE
b.[3-Mo Adj ADT] >= a.[3-Mo Adj ADT])\(SELECT COUNT(*) FROM [tbl_Final])
AS
[ADT Rank 3Month], 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS c WHERE
c.[6-Mo Adj ADT] >= a.[6-Mo Adj ADT])\(SELECT COUNT(*) FROM [tbl_Final])
AS
[ADT Rank 6Month], 1+(SELECT 10*COUNT(*) - 1 FROM [tbl_Final] AS d WHERE
d.[12-Mo Adj ADT] >= a.[12-Mo Adj ADT])\(SELECT COUNT(*) FROM
[tbl_Final])
AS [ADT Rank 12Month], a.[3-Mo Adj ADT], a.[6-Mo Adj ADT], a.[12-Mo Adj
ADT]
FROM tbl_Final AS a;



vanderghast said:
Have you tried to change the >= to <= in order to reverse the rank?

The constant 10 is because you want per-10-tiles; it would be 100 for
per-100-tiles, or 6 for per-6-tiles ( if that makes sense).


Vanderghast, Access MVP


slotmgr70 said:
Hi Vand,

I tried the query below and it produced results, but it doesn't seem to
be
ranking players according to their play level. There's no correlation
between
the ranking results and the actual play levels. For example, a player
with
low ADT (play) was given a rank of 1.

There are thousands of records, so should we be using 10 for the count?

Thanks,
Bill



:

If you don't have access to MS SQL Server 2005 (or later), you can
try:

---------------------
SELECT Player_ID,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS b WHERE b.[3-Mo
Adj
ADT] >= a.[3-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver3Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS c WHERE c.[6-Mo
Adj
ADT] >= a.[6-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver6Month,

1+(SELECT 10*COUNT(*) - 1 FROM [Raw Summary] AS d WHERE d.[12-Mo
Adj
ADT] >= a.[12-Mo Adj ADT]) \ (SELECT COUNT(*) FROM [Raw Summary]) AS
rankOver12Month

FROM [Raw Summary] AS a
-----------------------

(if my ( ) are right).

I assumed there is at least 10 records in the table.



Vanderghast, Access MVP



Hello,

I have a query of slot players that is sorted by their level of
play -
3-months, 6-months and 12-months - in ascending order. I would like
to
give
them a decile ranking from 1-10 based upon their 3-month ADT (play),
using
6-month and 12-month ADT as the "tie-breaker". In other words, the
lowest
10%
will get a "1" ranking, followed by the 2nd 10% getting a "2"
ranking,
and
so
on.

Here is my query:

SELECT [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
FROM [Raw Summary]
GROUP BY [Raw Summary].Player_ID, [Raw Summary].[3-Mo Adj ADT], [Raw
Summary].[6-Mo Adj ADT], [Raw Summary].[12-Mo Adj ADT]
ORDER BY [Raw Summary].[3-Mo Adj ADT], [Raw Summary].[6-Mo Adj ADT],
[Raw
Summary].[12-Mo Adj ADT];


Any help is appreciated.

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top