Complex Calculation

[RoRo]

>I'm fairly new to SQL server 2005

database. The client wants to use weighted parameters(5) or areas
of a project, rated from 1-5 on a scale of 1 being the highest, and
the Cost of each project.
i.e. Project A Param1=1, Param2=4 , Param3=1, Param4=2, Param5=5 , Cost= $50000
Using the least squares fit they want to be able to calculate a
projected cost for a project with same weighted parameters.
With this being said I was wondering what I could use to do this using the information from the SQL Server. >Example:

Currently this is a formula used for calculating the least square's fit using a matrixs
Each Row of completed Project Cost for a completed project
ex. Matrix A = 1, u1, v1, w1, x1 Matrix b = M1
1, u2, v2, w2, x2 M2
1, u3, v3, w3, x3 M3
1, un, vn, wn, xn Mn

where u,v,x are weighted parameters where M is a a project

x=(A^T * A)^-1 * (A^T * b) x is the function that defines the least
squares curve for the data set

[Erland Sommarskog]

RoRo (Ro***********@gmail.com) writes:

Just let me warn you this is very complex and I'm sorry If I don't
explain enough detail but if anyone could help I would greatly
appreciate it.
This is the Table that is created in my database::

The Insert Statement for the data is very complex b/c I'm getting the
data from radio buttons in my webform...

Writing the INSERT statements for the few rows you posted is not
very complex. Possibly a little tedious.

Anyway, I am not sure how it could have helped.

Just know it works and the information is store like this for a
Metric(Project with the ID of 26).
MetricParameters:::

ParamID MetricID ParamValue Description
4 26 1 rstk
2 26 5 sre6dtk
6 26 1 seu
3 26 2 setj
...

MetricsCost::::
metricID
PrjID

.................................................. ......................

ActualHours(Cost in Hours)

26
15
1589

So with that I need a table that ends up like this:

Project Name Weighed Parameters Actual
Cost
Sys Para Elect ME I&T Cost in Hours
M1 1 1 1 1 500

As I cannot we how you get from the above to this table. I understand
that ParamValue above are weights, but the weights has to be applied
to something.

x = (A^T*A)^ -1 * A^T*b T==transpose -1==inverse matrix

Hm, transposing matrix is not really something you want to do in
SQL. Rows and columns are quite different things.

Maybe you should read the data into client level and work there? (Or
in a CLR stored procedure.)

But I think I have even less understanding of what is going on, so I
may be completely off-base.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx