Top 5 percentile and 95 percentile

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Top 5 percentile and 95 percentile

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-07-02 : 09:41:56

So I have the following that shows me where a particular portolio manager falls in the universe of account/portfolio managers based on month to date returns:

WITH MarksRnkCnt AS
(
  SELECT pmname.Firstname + ' ' + pmname.Lastname as pm,ap.accountnumber, ap.mtdreturn,
    RANK() OVER(ORDER BY ap.mtdreturn) AS rnk,
    COUNT(*) OVER() AS cnt
  FROM abacuspmperformance ap 
left join dbo.InvestmentOfficers pmname on ap.pm = pmname.initials 
where
ap.mtdreturn is not null
and ap.mtdreturn <> '1' and ap.perfclass = 'totl' 
)
SELECT pm,accountnumber,round(mtdreturn,2),
  1.*(rnk-1)/(cnt-1) AS percentilerank
FROM MarksRnkCnt where pm = 'Jon Doe'
ORDER BY pm desc

So example, lets say I get back 200 accounts, I'm looking for a way to just grab the 5th (5%percentile) account and the 195th (95%percentil) account mtd percentage number. Now obviously the number of accounts can always change, but want to always just pull back the two results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 09:45:05

Have a look at the NTILE() windowed function. Especially NTILE(20), which creates values ranging from 1 to 20

SELECT	Number,
	NTILE(20) OVER (ORDER BY Number)
FROM	master..spt_values
WHERE	Type = 'P'

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-07-02 : 09:59:12

Thanks for the quick reponse, just not sure I'm understanding my output now. I have a new column with 1-20, but am unsure how I pickup just the 5th account and just the 195th account, since there are several 5's and several 15's??? Thanks for the help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:10:01

[code]SELECT Account,other fields...
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GrpNo ORDER BY Account DESC) AS Seq,*
FROM
(
SELECT NTILE(20) OVER (ORDER BY Account ASC) AS GrpNo,*
FROM YourTable
)t
WHERE GrpNo IN (1,19)
)r
WHERE r.Seq=1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 12:32:52

Or...

NTILE(100)

WHERE GrpNo ON (5, 95)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:52:00

quote:
Originally posted by Peso

Or...

NTILE(100)

WHERE GrpNo ON (5, 95)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

but would this return 1 record per percentile group as asked by OP when number of accounts vary?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 13:09:54

No, for 200 records ntile(100) returns 2 records per value.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-07-02 : 13:21:09

so now my question is, how do I have it determine what number it should be grabbing if i want the 5percentile number and the 95 percentile number. obviously based on the number of accounts that record number is going to change as far as which one I want to grab and display.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 13:33:39

what do you mean by percentile number? in the above case you will get more than one number in both 5th percentile and 95th percentile group

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-07-02 : 14:47:35

quote:
Originally posted by visakh16

what do you mean by percentile number? in the above case you will get more than one number in both 5th percentile and 95th percentile group

Ok, let me go back and look @ this a bit closer, thanks again for everyones help so far

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-07-21 : 12:09:02

So i'm finally getting back to this, in the below example, I have 266 accounts and Im using the following to get the 5th percentile and the 95th percentile accounts, and its a bit off on the 95th percentile, the record it brings back is more like the 90th percentile:

WITH MarksRnkCnt AS
(
  SELECT pmname.Firstname + ' ' + pmname.Lastname as pm,ap.accountnumber, ap.mtdreturn,ap.reportdt,
    RANK() OVER(ORDER BY ap.mtdreturn) AS rnk,
    COUNT(*) OVER() AS cnt
  FROM abacuspmperformance ap 
left join dbo.InvestmentOfficers pmname on ap.pm = pmname.initials 
where
ap.mtdreturn is not null
and ap.mtdreturn <> '1' and ap.perfclass = 'totl' and reportdt = '2009-07-20'
)
SELECT pm,accountnumber,round(mtdreturn,2) as MonthtoDateReturn,
  1.*(rnk-1)/(cnt-1) AS percentilerank, 'all' as Source
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GrpNo ORDER BY Accountnumber DESC) AS Seq,*
FROM
(
SELECT NTILE(20) OVER (ORDER BY Accountnumber ASC) AS GrpNo,*
FROM MarksRnkCnt
)t
WHERE GrpNo in (1, 19) and pm = 'Jon Doe' and reportdt = '2009-07-20'
)r
WHERE r.Seq=1

The number of accounts will always be different, but the two records we are trying to pull back are always just the 5th and 95th percentile. Please let me know if I can provide any more info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources