SQL equivalent to NORM.DIST Excell function?

Question

SQL equivalent to NORM.DIST Excell function?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

mharbuz Say Hey Kid Points: 678 More actions · Answer 1

Hi Jonathan,

Thank you very much for your help. I have created the tvf on our test database but I'm quite a novice when it comes to SQL so I don't really understand how to use the function, I specifically don't understand the "CROSS APPLY" bit. If I was to tell you that the column name for the data I want to apply this function to is called "TimeRecordedBalance" and the table name is "mattersarchive.CaseType" what exactly would I need to enter for this to work?

Sorry for the stupid question but I've never done anything this advanced before.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

A tvf is a function that returns a table. So you use it in the same way you would use a table. From how you describe the table I think you would need something like this:

SELECT *
  FROM dbo.Tally t
 CROSS APPLY (SELECT AVG((TimeRecordedBalance+0.0)/3200), VAR((TimeRecordedBalance+0.0)/3200)
                FROM mattersarchive 
               INNER JOIN casetypes 
                       ON mattersarchive.CaseType = CaseTypes.Description
               WHERE casetype NOT LIKE 'domestic %' 
                 AND mattersarchive.created > '2013-07-01' 
                 AND mattersarchive.description LIKE '%' + @Description + '%') X(Mean, Variance)
 CROSS APPLY dbo.tvfNORMALDIST(T.N, X.Mean, X.Variance) Y
 WHERE t.N BETWEEN 1 AND 60
GO

You will also need a tally table to generate the range of x coordinates you wish to plot. You can create one from a script in this article:

https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

Create a Tally table

--===== Conditionally drop 
     IF OBJECT_ID('dbo.Tally') IS NOT NULL 
        DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
 SELECT TOP 1000000 
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2,
        Master.dbo.SysColumns sc3
--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Let the public use it
  GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

mharbuz Say Hey Kid Points: 678 More actions · Answer 3

Hi Jonathan,

Thank you very much for all your help. I have eventually managed to create the bell curve in SSRS by using your equation as an expression in SSRS rather than doing it in the SQL query for the dataset.

I've had to slightly change the syntax for it to work in SSRS but it does work so thank you very much for that.

Here is the expression for anyone else who is wants to work out the Normal Distribution in SSRS:

=EXP(-POW(Fields!Time.Value-ReportItems!Mean.Value,2)/(2*ReportItems!Var.Value))/(SQRT(2*3.141592653589*ReportItems!Var.Value))

The Report Item "Mean" is the AVG of the Time (X-Axis) value and the Report Item "Var" is the Var of the Time (x axis).

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Hi mharbuz,

Thanks, I'm glad you got it to work eventually.

Jonathan