Solved

# Percentile function

Posted on 2002-06-28
584 Views
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
Question by:syamsundar
• 2
• 2

LVL 35

Expert Comment

Hi,

Are you using SQL 2000?

Can this be writtern as a user defined function?

Regards
David
0

Author Comment

Yes i am using sql 2000. A user defined function will do.
0

LVL 35

Accepted Solution

David Todd earned 50 total points
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

Author Comment

todd,

Thank you very much.
0

## Featured Post

### Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.