By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Complex Calculation

P: n/a
>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
Mar 31 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Apr 1 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.