This percentile function takes a looooong time.

Using 10,000 rows of data it took 2 minutes and 10 seconds on my computer.

This is how to get the 10,000 rows into the table:

Declare @TestScores table (StudentID int identity(1,1), Score int)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (03)

insert @TestScores (Score) Values (40)

insert @TestScores (Score) Values (45)

insert @TestScores (Score) Values (50)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (90)

insert @TestScores (Score) Values (20)

insert @TestScores (Score) Values (11)

insert @TestScores (Score) Values (30)

while @@ROWCOUNT > 0

insert @TestScores select t.Score from @TestScores t

cross join

(select max (StudentID) MaxRowNum from @TestScores) x

where

t.StudentID <= 10000 - x.MaxRowNum

That code puts the same scores in 1000 times, so you should get the same results as in the original article.

Here is a modification that is a bit faster (takes less than 1 second):

declare @pp float

set @pp = .5

declare @k int, @d float, @ax float, @bx float

declare @values table (i int identity(1,1), x float)

insert @values select Score from @TestScores order by Score

select @k=floor(kf), @d=kf-floor(kf)

from (select 1+@pp*(count(*)-1) as kf from @values) as x1

select @ax=x from @values where i=@k

select @bx=x from @values where i=@k+1

select @pp as factor,

dbo.LERP(@d, 0.0, 1.0, @ax, @bx) as percentile

Personally I think LINTERP would be a nicer name than LERP.