Query - formulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query - formulation

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-03-01 : 09:55:18

Hi,

I have 2 tables

TblTemp

z1 z2 z3 z4
0.20 0.30 0.40 0.50

TblBusType

C1 C2 C3 C4
A 1 2 3 4
B 2 3 4 5
C 3 4 5 6
D 4 5 6 7

Now, I need to compute the following;

(A/B) z1 x (A/B) z2 x (A/B) z3 x (A/B) z4 as result1
(A/C) z1 x (A/C) z2 x (A/C) z3 x (A/C) z4 as result2
(A/D) z1 x (A/D) z2 x (A/D) z3 x (A/D) z4 as result3
(B/C) z1 x (B/C) z2x (B/C) z3 x (B/C) z4 as result4
(B/D) z1 x (B/D) z2x (B/D) z3 x (B/D) z4 as result5
(C/D) z1 x (C/D z2 x (C/D) z3 x (C/D) z4 as result6

the above can also be represented with values as

(1/2)0.20 x (2/3)0.30 x (3/4)0.40 x (4/5)0.50 as result1
(1/3)0.20 x (2/4)0.30 x (3/5)0.40 x (4/6)0.50 as result2
(1/4)0.20 x (2/5)0.30 x (3/6)0.40 x (4/7)0.50 as result3
(2/3)0.20 x (3/4)0.30 x (4/5)0.40 x (5/6)0.50 as result4
(2/4)0.20 x (3/5)0.30 x (4/6)0.40 x (5/7)0.50 as result5
(3/4)0.20 x (4/5)0.30 x (5/6)0.40 x (6/7)0.50 as result6

N/B: (A/B) z1 is supposed to be (A/B)to the power z1 and so on for the rest of the query. Couldn't do it here...

Note: This particular example is based on 4 records A, B, C & D. I will want to do it for nearly 200 combinations. Therefore, the query should accomodate more records.

Thank you very much

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 10:24:31

What is A and B???

Are the Values in a column?

You only list 4 clumns in tblBusType but you have 5 values?

And doing Math Between rows makes very little sense (if that's what you are doing) because the order of rows of data in a database has no meaning

Can you explain a little better what you are trying to do?

Does tbltemp have only 1 Row?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-03-01 : 10:39:07

TblBusType

BusType C1 C2 C3 C4
A 1 2 3 4
B 2 3 4 5
C 3 4 5 6
D 4 5 6 7

A,B,C & D are Business types with 4 criterias each.

Tbltemp has only one row with 4 fields.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 10:47:37

sounds like this


SELECT (bt1.C1*1.0/bt2.C1)*t.z1 * (bt1.C2*1.0/bt2.C2)*t.z2 * (bt1.C3*1.0/bt2.C3)*t.z3 * (bt1.C4*1.0/bt2.C4)*t.z4
FROM TblBusType bt1
CROSS JOIN TblBusType bt2
CROSS JOIN TblTemp t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 10:48:44

quote:
Originally posted by visakh16

sounds like this

SELECT (bt1.C1*1.0/bt2.C1)*t.z1 * (bt1.C2*1.0/bt2.C2)*t.z2 * (bt1.C3*1.0/bt2.C3)*t.z3 * (bt1.C4*1.0/bt2.C4)*t.z4
FROM TblBusType bt1
CROSS JOIN TblBusType bt2
CROSS JOIN TblTemp t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This makes sense to you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-03-01 : 10:54:07

N/B: (A/B)z1 is supposed to be (A/B)to the power z1 and so on for the rest of the query. Couldn't do it here...

Again, thats only for result1 - I'll require a more robust query to accomodate 200 cominations instead of me typing in the first combination...

Thanks for all your inputs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources