• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

Percentile function

HI

I badly need a percentile function, it something like this

 getPercentile (value,set)

I am hoping that there might be an extended stored
procedure or something similar that we can use.

Currently we do not have the option of using SQL 2000
Analysis Services.

Any help is greatly appreciated.

Thanks,
syam
0
syamsundar
Asked:
syamsundar
  • 2
  • 2
1 Solution
 
David ToddSenior DBACommented:
Hi,

Are you using SQL 2000?

Can this be writtern as a user defined function?

Regards
  David
0
 
syamsundarAuthor Commented:
Yes i am using sql 2000. A user defined function will do.
0
 
David ToddSenior DBACommented:
Hi,

Isn't the percentile just the score / possible * 100?

Is this what you are looking for?

drop function Percentile
go

create function Percentile( @score as integer, @Possible as integer )
returns integer
as
begin
     declare @Percent     integer

     if @Possible != 0
          set @Percent = ( cast( @Score as float ) * 100.0 / cast( @Possible as float ))
     else
          set @Percent = 0

     return( @Percent )
 
end

select dbo.Percentile( 43, 233 ) as percentile


gives

percentile  
-----------
18

(1 row(s) affected)

Note that I had to cast the parameters to floats to get the division to work, and also moved the * 100 to get better accuracy.

Regards
  David
0
 
syamsundarAuthor Commented:
todd,

Thank you very much.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now