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

[JC]

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

[Russell Fields]

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/library/aa178231(SQL.80).aspx

RLF

"JC" <J...@discussions.microsoft.com> wrote in message
news:ABB4BFCE-4940-4F4F...@microsoft.com...

[timpickering]

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.09678418+@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,

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,

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/library/aa178231(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/aspnet/f7338bb9-7fa4-4fa8-9e5a-244857b0d9d4/six-free-visual-studio-2010-msdn-memberships-giveaway.aspx

[Robert Bronaugh]

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 Thursday, October 25, 2007 11:21 AM J wrote:

> 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. Here
>> is an Excel / SQL Server page for SQL Server 2000.
>> http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx
>>
>> RLF

>>> Submitted via EggHeadCafe - Software Developer Portal of Choice

>>> ASP.NET Providerless Custom Forms Authentication, Roles and Profile with MongoDb
>>> http://www.eggheadcafe.com/tutorials/aspnet/27f836b7-2c9e-4942-9712-1c7b901cadcc/aspnet-providerless-custom-forms-authentication-roles-and-profile-with-mongodb.aspx

[ple...@hotmail.com]

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.