Caclulating two-tailed Student's t-distribution

Question

Post reply

Caclulating two-tailed Student's t-distribution

jnp15

SSC-Addicted

Points: 497
More actions
September 4, 2013 at 8:58 am

#279428

I would like to calculate the two-tailed Student's t-distribution from within T-SQL. Excel does this with it's TINV() function. I want to be able to pass it the degrees of freedom and spedify the probability level and get returned the t-distribution value. Is there some way of either calling the excel function or recreating it in T-SQL?
TIA,
Jon

Viewing 4 posts - 1 through 3 (of 3 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi Jon,

You have 2 ways of calculating the value.

1. You can use sp_OACreate to create an instance of Excel in SQL Server and refer to Excel function.

Please review an article on this site

http://www.sqlservercentral.com/articles/Excel/64838/

Automating Excel from SQL Server

By Wayne Sheffield, 2010/02/19 (first published: 2008/12/05)

2. You can create a VB or C# CLR stored procedure in SQL Server. C# and VB.NET code for your TINV function is in the article

http://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.worksheetfunction.tinv.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

TInv method

Yelena

Regards,Yelena Varsha

jnp15 SSC-Addicted Points: 497 More actions · Answer 2

Thanks for your response Yelena. I think this is on the right track. I don't really know VB or C so I am thinking that calling Excel from SQL is probably the way for me to go. The article that you pointed me to explains how to populate an Excel spreadsheet with SQL data. What I would like to do is be able to use an excel function within SQL to get results. For example something like the following:

SELECT @Result=TINV(@SL,@DF)

where I have declared and assigned these variables values earlier in SQL

The logic of doing this may be buried in the article somewhere but I'm not quite seeing it. Could you clarify a bit more?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I found the articles:

http://msdn.microsoft.com/en-us/library/office/ff834434.aspx

WorksheetFunction Object (Excel)

The example they give is to use

Application.WorksheetFunction.Min(myRange)

to use say, Min function. You can try your function.

http://msdn.microsoft.com/en-us/library/office/ff822194.aspx

WorksheetFunction Members (Excel)

http://msdn.microsoft.com/en-us/library/office/ff837360.aspx

WorksheetFunction.TInv Method (Excel)

The last link describes your function, does not have an example but have remarks.

This has been found in Excel Object Model reference for Office 2013 and 2010 that has to be installed on the system where youa re running your SQL Server. I did not see this function in the earlier versions like 2007.

Yelena

Regards,Yelena Varsha