August 24, 2009 at 2:00 am
In Excel you get a function called NORMSDIST(z)
It returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.
Is there a way of doing the same in SQL?
I have done rather wide searched on the internet for a solution but couldn't something that comes close to a plausible solution.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
August 24, 2009 at 10:11 am
should be doable, sure. if you can post the equation in question, then it should be easy enough to apply it to the result set and transform it accordingly. my stats is pretty crummy, so i can't recall the equation, but if you provide it i can see what i can do for you.
August 24, 2009 at 11:32 am
SQL Server has the following ingredients to your function. EXP, PI, STDEV, AVG, SQRT
http://en.wikipedia.org/wiki/Normal_distribution
I think the rest is making sure the formula's order of operations plays out and that you're looking at the right formula. Since I have no experience with this, I'll leave that to you and wikipedia.
~BOT
Craig Outcalt
August 24, 2009 at 12:05 pm
Frankly this is one where I would go CLR. In other words - write it in C# or VB and use it in SQL. You will have a LOT more luck (as far as I know it's simply a matter of writing a wrapper for the existing NORMDIST function already there).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 25, 2009 at 12:57 am
SQLBOT: I actually have written that function from the formula on Wikipedia. But for some re3ason it doesn't bring back the correct result. Not even close.
POWER(1/SQRT(2*PI()),-1*(POWER([@BINVALUE],2)/2))
Matt: I actually found that when using SQL Analysis Services using MDX ALL the excel functions are at my disposal.
JUST A THOUGHT - Why cant MS SQL be launched with basic financial function that make it easier for developers - WE ARE NOT STATISTICAL GENIUSES.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
August 25, 2009 at 5:13 am
Seems like I spoke to soon. The following ESSENTIAL Excel functions are not in SSAS-2K5.
Standardize
NormDist
NormsDist
Do anybody now whether there are function like those in SSAS-2K5?
http://msdn.microsoft.com/en-us/library/aa178231(SQL.80).aspx
The above link explains that these functions are indeed there.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply