Hi,
I'm relatively new to MS SQL Server.
Excel has NORM.S.DIST and NORM.S.INV formulas, I need to insert the equivalent into SQL code, I presume as a function.
Is there anyone out there who share to code with me?
NewBee
Hi,
I'm relatively new to MS SQL Server.
Excel has NORM.S.DIST and NORM.S.INV formulas, I need to insert the equivalent into SQL code, I presume as a function.
Is there anyone out there who share to code with me?
NewBee
hi
please see if the following links helps !!!!
hope it helps
Thanks harishgg1,
I'm really battling to obtain the NORM.S.INV, the link in the link you posted doesn't work.
Any Idea on how I get the NORM.S.INV code?
found this online somewhere.
select dbo.normsinv(0.9999)
result => 3.71901648212512
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create FUNCTION dbo.normsinv (@P float)
RETURNS float
as
begin
declare @a1 float
declare @a2 float
declare @a3 float
declare @a4 float
declare @a5 float
declare @a6 float
declare @b1 float
declare @b2 float
declare @b3 float
declare @b4 float
declare @b5 float
declare @c1 float
declare @c2 float
declare @c3 float
declare @c4 float
declare @c5 float
declare @c6 float
declare @d1 float
declare @d2 float
declare @d3 float
declare @d4 float
declare @plow float
declare @phigh float
declare @q float
declare @r float
declare @result float
Set @a1 = -39.6968302866538
Set @a2 = 220.946098424521
Set @a3 = -275.928510446969
Set @a4 = 138.357751867269
Set @a5 = -30.6647980661472
Set @a6 = 2.50662827745924
Set @b1 = -54.4760987982241
Set @b2 = 161.585836858041
Set @b3 = -155.698979859887
Set @b4 = 66.8013118877197
Set @b5 = -13.2806815528857
Set @c1 = -7.78489400243029E-03
Set @c2 = -0.322396458041136
Set @c3 = -2.40075827716184
Set @c4 = -2.54973253934373
Set @c5 = 4.37466414146497
Set @c6 = 2.93816398269878
Set @d1 = 7.78469570904146E-03
Set @d2 = 0.32246712907004
Set @d3 = 2.445134137143
Set @d4 = 3.75440866190742
set @plow=.02425
set @phigh=1-@plow
if (@p<@plow)
begin
set @q = Sqrt(-2 * Log(@p))
set @result=(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)
end
else
begin
if (@p<@phigh)
begin
set @q =@p - 0.5
set @r = @q * @q
set @result= (((((@a1 * @r + @a2) * @r + @a3) * @r + @a4) * @r + @a5) * @r + @a6) * @q / (((((@b1 * @r + @b2) * @r + @b3) * @r + @b4) * @r + @b5) * @r + 1)
end
else
begin
set @q = Sqrt(-2 * Log(1 - @p))
set @result= -(((((@c1 * @q + @c2) * @q + @c3) * @q + @c4) * @q + @c5) * @q + @c6) / ((((@d1 * @q + @d2) * @q + @d3) * @q + @d4) * @q + 1)
end
end
return @result
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO