95th percentile and Median functions in SQL server 2008/2005

[A Ahmed]

Could some one guide if I can write code for 95th percentile or median function in SQL server 2008. My work place reports are very complex and we need an urgent solution to find out these fuctions?
If some one got dll file to assemble with SQL server that will be an ideal one to run this function as build in?

Regards
Ali

[Rabbit]

You don't need a function per se. If you wanted, you could write a function or a stored procedure. But you could do it by selecting the last record of the top 50 percent and the first record of the bottom 50 percent. Then you add them and divide by two.

A median is really a 50th percentile. To get a different percentile, you just need to use different numbers. For 95th percentile, you need the last record of the top 5 percent and the first record of the bottom 95 percent.

[nbiswas]

Hi,
Try this
Consider the sample input.(tblInput)

Numbers
10
20
30
40
50

The ddl is as under

Declare @tblInput table(Numbers int)
Insert into @tblInput select  10 union all select 20 union all select 30 union all select 40 union  all select 50

Objective
To get the 95th percentile

Method to get so : Nearest rank.

Formula:

n = p/100 * N  + 0.5

So, for our example, the result will be
n= 95/100*5+0.5 =5.25
so the 95th percentile would be 50, the fifth number (since 5.25 rounds up to 5).

Declare @p int
Declare @N int
Declare @n numeric
Set @p = 95
Select @N = count(*) from @tblInput
Select  @p/100 * @N + 0.5 

You can make this as a function and reuse it.

Hope this helps.

Reference:
Percentile