Normal Distribution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Normal Distribution

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-28 : 08:19:06

Hi Guys

How i can use Normal Distribution in SQL SERVER 2005 is there any functions .

please help out

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 10:08:49

There are functions for standard deviation (STDEV,STDEVP) and variance (VAR,VARP) and average (AVG)

Jim

Everyday I learn something that somebody else already knew

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-28 : 10:53:48

HI Jimf

How i can use those things can you please explain me it would be helpful for me

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 11:17:18

Books On Line can explain them much better than I can.

Jim

Everyday I learn something that somebody else already knew

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-28 : 11:26:18

Hi

No am asking
How i can use(STDEV,VAR,VAP,AVG...etc..)those conbinations to get NORMSDIST

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 11:33:15

NORMSDIST is an Excel function. Are you asking me to write a sql function that does the same thing?

Jim

Everyday I learn something that somebody else already knew

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-29 : 01:03:23

Yes
I want to write a SQL function

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-29 : 01:26:02

Hi

Try this...


CREATE FUNCTION GET_NORMSDIST_CALC
(
    @x float
)
    RETURNS FLOAT
AS
BEGIN 
	declare @result float
    declare @L float
    declare @K float
    declare @dCND float
    declare @pi float
    declare @a1 float
    declare @a2 float
    declare @a3 float
    declare @a4 float
    declare @a5 float

    select @L = 0.0
    select @K = 0.0
    select @dCND = 0.0

    select @a1 = 0.31938153
    select @a2 = -0.356563782
    select @a3 = 1.781477937
    select @a4 = -1.821255978
    select @a5 = 1.330274429
    select @pi = 3.1415926535897932384626433832795

    select @L = Abs(@x)

   if @L >= 30
    begin
        if sign(@x) = 1
            select @result = 1
        else
            select @result = 0
    end
    else
    begin
    -- perform calculation
        select @K = 1.0 / (1.0 + 0.2316419 * @L)
        select @dCND = 1.0 - 1.0 / Sqrt(2 * @pi) * Exp(-@L * @L / 2.0) *
          (@a1 * @K + @a2 * @K * @K + @a3 * POWER(@K, 3.0) + @a4 * POWER(@K, 4.0) + @a5 * POWER (@K, 5.0))
        if (@x < 0)
            select @result = 1.0 - @dCND
        else
        SELECT @result = @dCND
		
	end
		RETURN @result
END

-------------------------
R...

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-29 : 02:02:44

Hi rajdaksha

its working fine...thanks....

lcerni
Starting Member

2 Posts

Posted - 2010-08-15 : 16:04:43

Is there a SQL Server 2005 equivalant function to excel's function normsdist?

I have found this function, but I am having round off issues. I am hoping that someone with a better pair of eyes can see it. I have tried various things like changing FLOAT TO DECIMAL(28,16), for example. I was wondering if it would help if I expanded the @a1 through @a5 values to a decimal place of 16, however, I do not know where these numbers come from. Can 0.31938153 be expanded to 16 decimal places and where would I find this information? I am required to store to the 8th decimal place. But the 7th and 8th decimal places aren't matching.

For example, the excel spreadsheet is calculating 0.600112743676456 but the SQL Server side is calculating 0.60011269.

CREATE FUNCTION [dbo].[udf_NORMSDIST]
(
@x FLOAT
)
RETURNS FLOAT
AS
/****************************************************************************************
NAME: udf_NORMSDIST
WRITTEN BY: rajdaksha
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135026
DATE: 2009/10/29
PURPOSE: Mimics Excel's Function NORMSDIST

Usage: SELECT dbo.udf_NORMSDIST(.5)

REVISION HISTORY

Date Developer Details
2010/08/11 LC Posted Function

*****************************************************************************************/

BEGIN
DECLARE @result FLOAT
DECLARE @L FLOAT
DECLARE @K FLOAT
DECLARE @dCND FLOAT
DECLARE @pi FLOAT
DECLARE @a1 FLOAT
DECLARE @a2 FLOAT
DECLARE @a3 FLOAT
DECLARE @a4 FLOAT
DECLARE @a5 FLOAT

--SELECT @L = 0.0
SELECT @K = 0.0
SELECT @dCND = 0.0

SELECT @a1 = 0.31938153
SELECT @a2 = -0.356563782
SELECT @a3 = 1.781477937
SELECT @a4 = -1.821255978
SELECT @a5 = 1.330274429
SELECT @pi = 3.1415926535897932384626433832795

SELECT @L = Abs(@x)

IF @L >= 30
BEGIN
IF sign(@x) = 1
SELECT @result = 1
ELSE
SELECT @result = 0
END
ELSE
BEGIN
-- perform calculation
SELECT @K = 1.0 / (1.0 + 0.2316419 * @L)
SELECT @dCND = 1.0 - 1.0 / Sqrt(2 * @pi) * Exp(-@L * @L / 2.0) *
(@a1 * @K + @a2 * @K * @K + @a3 * POWER(@K, 3.0) + @a4 * POWER(@K, 4.0) + @a5 * POWER (@K, 5.0))
IF (@x < 0)
SELECT @result = 1.0 - @dCND
ELSE
SELECT @result = @dCND

END

RETURN @result
END

dormand
Starting Member

1 Post

Posted - 2010-09-23 : 09:59:48

This is brilliant and helped me out quite a bit on a project. Thanks.

Dean Dorman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources