Can someone kindly assist me with converting the below two PLsql queries (NORM DIST & NORMINV) to MS SQL?
1st --> NORM DIST
CREATE OR REPLACE FUNCTION ATOM.GetNORMSDIST(ip_DistributionAt Number)
RETURN Number AS
b1 Number := 0.319381530;
b2 Number := -0.356563782;
b3 Number := 1.781477937;
b4 Number := -1.821255978;
b5 Number := 1.330274429;
p Number := 0.2316419;
c Number := 0.39894228;
t Number;
x Number := -10.1;
result Number;
BEGIN
if ip_DistributionAt >= 0.0 then
t := 1.0 / ( 1.0 + p * ip_DistributionAt );
result := (1.0 - c * exp( -ip_DistributionAt * ip_DistributionAt / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
else
t := 1.0 / ( 1.0 - p * ip_DistributionAt );
result := ( c * exp( -ip_DistributionAt * ip_DistributionAt / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ));
end if;
Return result;
END;
2nd ---> NORMINV
CREATE OR REPLACE FUNCTION ATOM.GetNORMSINV(ip_Probability Number)
RETURN Number AS
result number;
a1 Number := -39.6968302866538;
a2 Number := 220.946098424521;
a3 Number := -275.928510446969;
a4 Number := 138.357751867269;
a5 Number := -30.6647980661472;
a6 Number := 2.50662827745924;
b1 Number := -54.4760987982241;
b2 Number := 161.585836858041;
b3 Number := -155.698979859887;
b4 Number := 66.8013118877197;
b5 Number := -13.2806815528857;
c1 Number := -7.78489400243029E-03;
c2 Number := -0.322396458041136;
c3 Number := -2.40075827716184;
c4 Number := -2.54973253934373;
c5 Number := 4.37466414146497;
c6 Number := 2.93816398269878;
d1 Number := 7.78469570904146E-03;
d2 Number := 0.32246712907004;
d3 Number := 2.445134137143;
d4 Number := 3.75440866190742;
plow Number := 0.02425;
phigh Number := 0.97575;
q number;
r number;
BEGIN
BEGIN
If ip_Probability < plow
then
begin
q := Sqrt(-2 * ln(ip_Probability));
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 ip_Probability < phigh
then
begin
q := ip_Probability - 0.5;
r := q * q;
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
q := Sqrt(-2 * ln(1 - ip_Probability));
result := -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / ((((d1 * q + d2) * q + d3) * q + d4) * q + 1);
end;
end if;
end;
END IF;
end;
RETURN result;
I work closely with a team who run numbers on an Oracle database (Toad). They are not very collaborative tbh.
The above code is their NORMDIST and NORM INV code. When I run the code gleaned from my previous group my output is different. So I though I might post the oracle code to see if I can get it converted to MS SQL.