Patron Saint of Lost Yaks

SQLCLR aggregate function

Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.

Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location

Regression.dll http://regression.developerworkshop.net/dws.Regression.dll
Install.sql http://regression.developerworkshop.net/Install.sql
Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression)
Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression)
Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression)

Update:
* I have created a homepage for this SQLCLR function http://regression.developerworkshop.net

For the Sample2 above, the result for c: drive on Server1 look like this

<dws bestfit="linear" r2="0.99" type="least square regression">
<linear A="15417024.67" b="-388.60" r2="0.99" y="A+b*x" x="(y-A)/b" />
<exponential A="1.9E+102" b="-0.00"r2="0.98" y="A*e^(b*x)" x="ln(y/A)/b" />
<logarithmic A="162488444.70" b="-15345855.35" r2="0.99" y="A+b*ln(x)" x="e^((y-A)/b)" />
<power A="INF" b="-224.35" r2="0.98" y="A*x^b" x="e^(ln(y/A)/b)" />
</dws>

For Sample3 above, the result can look like

<dws a="-4.00"b="6.64"c="1.64"r2="0.98"y="a + b*x + c*x^2 + d*x^3" />

With these constants, you can easy calcuate what freespace there will be on a certain date, or on which date the freespace is x megabytes.

It has an ugly hack, because it seems VS2008 doesn't accept multiple parameters for Accumulate routine. I have to wait until VS2010 has fixed the strong name bug before doing this the right way.
I also have begun to code routines for Polynomial Regression too, along with Circle, Ellipse and parabolic regression. My idea is that this library of statistical and mathematical functions will continue to extend.

Nevertheless, the ugly hack works, and I would love some feedback.

There are plenty of uses for this routine. For example, if you store disk space amount in a history table, apply the BestFit formula on top of the data, and you can predict when space is out, or at least when space will be at a certain level in the future.

If you can find more uses, please let me know. And thanks to Mladen Prajdic and Florian Reischl for the last pieces of the xml return puzzle.

Cheers!