>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

RoRo (RonaldaLajuan@gmail.com) writes:
>>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

It was looooong ago I did least squares, so I'm fairly rusty on it. And
I have some difficulties to understand your matrixes.

The usual recommendation is that you post:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.




--
Erland Sommarskog, SQL Server MVP, esquel@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

Just let me warn you this is very complex and I'm sorry If I don't
explain enough detail but if anyone could 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...
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
5 26 2
1 26 5 sdth
7 26 4
12 26 2
13 26 4
9 26 1 sedhj
8 26 4
10 26 4
11 26 2
14 26 2
16 26 3
15 26 2
18 26 1 sedrhe
17 26 3

Of course I don't really need the description of the projects but the
ParamValue(Column) is always a weighted number between 1-5, the
ParamID is the ID number given to all of the Parameter for an Item
Type. i.e Item Antenna has 18 weighted Parameters. With a ProjectID or
MetricID of 26.

The cost for the Metric 26 is also put into another table called
MetricCost.

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
M2 1 2 1 1 870
M3 2 2 1 1 1000
M4 2 2 1 2 1750
M5 3 3 2 1 2000
M6 3 3 3 1 2500
M7 4 4 3 2 5000
M8 4 4 3 3 6200
M9 4 5 5 5 15000
M10 5 5 5 5 150050

And with this I take a New Matrix we will say EstimatedMatrix with the
same # parameter and try to calculate the cost for the new Project
based on other like it in the database.

New Project Weights Calculated Cost in Hours
1 1 1 1 494
1 3 1 1 1131
1 1 2 2 4306
2 2 1 2 2336
2 2 2 2 3971
2 3 3 2 5925
3 3 3 4 9626
3 4 3 4 9945
4 4 3 4 9292
4 5 5 5 15057
5 5 5 5 14404

The formula above for this calculation was given to me by the client.
I'm just not sure how to go about tackling this complex
calculation....



Each Row is a Metric for a completed
project Cost for a completed project


A= 1, u1, v12, ...,
x1m b= M1

1, u2, v22, ...,
x2m M2
...
1, un vn2 ...
xnm M3
where M is a project

where u,v, ...x are weighted parameters


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

x is function that defines the least squares curve for the data set

Cost of new bid = V^T*x T==>transpose

Weighted Parameters of New Bid

V = (1, ubid, vbid2, ..., xbidm )



I'm not asking for anyone to do this for me, I just have no idea where
to start. Can I write a stored procedure or function? How do I get
this information stored the way I need it to be stored for
calculations. It should also be known that I'm using visual web
developer b/c the client wants this posted on their intranet system.










On Mar 31, 4:23 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> RoRo (RonaldaLaj...@gmail.com) writes:
> >>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
>
> It was looooong ago I did least squares, so I'm fairly rusty on it. And
> I have some difficulties to understand your matrixes.
>
> The usual recommendation is that you post:
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o The desired result given the sample.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online
> for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

On Mar 31, 6:43pm, RoRo <RonaldaLaj...@gmail.com> wrote:

Sorry google messed up my formating on the tables.

> Just let me warn you this is very complex and I'm sorry If I don't
> explain enough detail but if anyone could 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...
> 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
> 5 26 2
> 1 26 5 sdth
> 7 26 4
> 12 26 2
> 13 26 4
> 9 26 1 sedhj
> 8 26 4
> 10 26 4
> 11 26 2
> 14 26 2
> 16 26 3
> 15 26 2
> 18 26 1 sedrhe
> 17 26 3
>
> Of course I don't really need the description of the projects but the
> ParamValue(Column) is always a weighted number between 1-5, the
> ParamID is the ID number given to all of the Parameter for an Item
> Type. i.e Item Antenna has 18 weighted Parameters. With a ProjectID or
> MetricID of 26.
>
> The cost for the Metric 26 is also put into another table called
> MetricCost.
>
> 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
> M2 1 2 1 1 870
> M3 2 2 1 1 1000
> M4 2 2 1 2 1750
> M5 3 3 2 1 2000
> M6 3 3 3 1 2500
> M7 4 4 3 2 5000
> M8 4 4 3 3 6200
> M9 4 5 5 5 15000
> M10 5 5 5 5 150050
>
> And with this I take a New Matrix we will say EstimatedMatrix with the
> same # parameter and try to calculate the cost for the new Project
> based on other like it in the database.
>
> New Project Weights Calculated Cost in Hours
> 1 1 1 1 494
> 1 3 1 1 1131
> 1 1 2 2 4306
> 2 2 1 2 2336
> 2 2 2 2 3971
> 2 3 3 2 5925
> 3 3 3 4 9626
> 3 4 3 4 9945
> 4 4 3 4 9292
> 4 5 5 5 15057
> 5 5 5 5 14404
>
> The formula above for this calculation was given to me by the client.
> I'm just not sure how to go about tackling this complex
> calculation....
>
> Each Row is a Metric for a completed
> project Cost for a completed project
>
> A= 1, u1, v12, ..., x1m b= M1
>
> 1, u2, v22, ..., x2m M2
> ...
> 1, un vn2 ..., xnm M3
> where M is a project
>
> where u,v, ...x are weighted parameters
>
> x = (A^T*A)^ -1 * A^T*b T==> transpose -1==> inverse matrix
>
> x is function that defines the least squares curve for the data set
>
> Cost of new bid = V^T*x T==>transpose
>
> Weighted Parameters of New Bid
>
> V = (1, ubid, vbid2, ..., xbidm )
>
> I'm not asking for anyone to do this for me, I just have no idea where
> to start. Can I write a stored procedure or function? How do I get
> this information stored the way I need it to be stored for
> calculations. It should also be known that I'm using visual web
> developer b/c the client wants this posted on their intranet system.
>
> On Mar 31, 4:23 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
> > RoRo (RonaldaLaj...@gmail.com) writes:
> > >>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
>
> > It was looooong ago I did least squares, so I'm fairly rusty on it. And
> > I have some difficulties to understand your matrixes.
>
> > The usual recommendation is that you post:
> > o CREATE TABLE statements for your tables.
> > o INSERT statements with sample data.
> > o The desired result given the sample.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online
> > for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

RoRo (RonaldaLajuan@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 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 ed.

> 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, esquel@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