Hi,

In Excel I can find the normal distribution with the formula:

=NORMDIST(x, mean, standard dev, cumulative)

How is this done in SQL 2005? I am having a difficult time finding this on
the web so hopefully someone can assist me with this or at least point me in
the right direction.

Thank you in advance for your help.

All the best,

JC

JC,

SQL Server implements functions like this through Analysis Services. Here
is an Excel / SQL Server page for SQL Server 2000.
http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

RLF

"JC" <JC (AT) discussions (DOT) microsoft.com> wrote

Here is a pretty close version I wrote in T/SQL:

create function normdist(@value float,
@mean float,
@sigma float,
@cummulative bit)
returns numeric(12,2)
begin

declare @x float
declare @z float
declare @t float
declare @ans float
declare @returnvalue float


select @x = (@value-@mean)/@sigma
if (@cummulative = 1)
begin
select @z = abs(@x)/sqrt(2.0)
select @t = 1.0/(1.0+0.5*@z)
select @ans = @t*exp(-@z*@z-1.26551223+@t*(1.00002368+@t*(0.37409196+@t*(0.096 78418+@t*(-0.18628806+@t*(0.27886807+@t*(-1.13520398+@t*(1.48851587+@t*(-0.82215223+@t*0.17087277)))))))))/2.0
if (@x <= 0)
select @returnvalue = @ans
else
select @returnvalue = 1-@ans
end
else
begin
select @cummulative = exp(-@x*@x/2.0)/sqrt(2.0*3.14159265358979)
end

return cast(@returnvalue * 100 as numeric(12,2))

end




J wrote:

NORMDIST(X, mean, Standard_Dev,True) in SQL 2005?
25-Oct-07

Hi,

In Excel I can find the normal distribution with the formula:

=NORMDIST(x, mean, standard dev, cumulative)

How is this done in SQL 2005? I am having a difficult time finding this on
the web so hopefully someone can assist me with this or at least point me in
the right direction.

Thank you in advance for your help.

All the best,

JC

Previous Posts In This Thread:

On Thursday, October 25, 2007 11:21 AM
J wrote:

NORMDIST(X, mean, Standard_Dev,True) in SQL 2005?
Hi,

In Excel I can find the normal distribution with the formula:

=NORMDIST(x, mean, standard dev, cumulative)

How is this done in SQL 2005? I am having a difficult time finding this on
the web so hopefully someone can assist me with this or at least point me in
the right direction.

Thank you in advance for your help.

All the best,

JC

On Thursday, October 25, 2007 11:46 AM
Russell Fields wrote:

JC,SQL Server implements functions like this through Analysis Services.
JC,

SQL Server implements functions like this through Analysis Services. Here
is an Excel / SQL Server page for SQL Server 2000.
http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

RLF


Submitted via EggHeadCafe - Software Developer Portal of Choice
Six Free Visual Studio 2010 MSDN Memberships Giveaway
http://www.eggheadcafe.com/tutorials...-giveaway.aspx

This one is cumulative = FALSE (aka Probability Density Function) also in T-SQL

CREATE FUNCTION [dbo].[NORMDIST]
(
@x float
,@xBar float
,@Sigma float
)
RETURNS decimal(6,5)
AS
BEGIN
DECLARE @ProbDensity as decimal(6,5)
SELECT @ProbDensity =
ROUND(
(1/
sqrt(2 * pi() * square(@Sigma))
)
* exp(-(square((@x - @xBar))
/(2*square(@sigma))
)
)
,5)
RETURN @ProbDensity
END

On Friday, October 26, 2007 1:21:00 AM UTC+10, JC wrote:
Hi. I realise this is an old post but I've created an SQL library that includes exactly what you are after. In fact it does it for a dozen or so distributions. It's at sqladmintools.com and called SQLMath.