Adding mutiple columns - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Adding mutiple columns

Author

Topic

rajesh_
Starting Member

8 Posts

Posted - 2007-06-11 : 08:30:33

HI,
I have two tables A and B with following Data

Table A
------------

ID(PK) | V | W | X | Final
----------------------
1 | 4 | 5 | 6 | output

Table B
------------

ID | Par_ID (FK) | Cost_Per_Annum
------------------------
1 | 1 | 12000
2 | 1 | 24000
3 | 1 | 14000

Output should be calculated using below the formula .

output = Column V * 1st record of par_id=1 + Column w * 2nd record of par_id=1 + Column X * 3rd record of par_id=1

output = 4* 12000 + 5* 24000 + 6 * 14000

How can i do like this i have tried several ways and failed. Please suggest me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 08:48:55

[code]
select a.ID, a.V, a.W, a.X,
Final = (a.V * b1.Cost_Per_Annum) +
(a.W * b2.Cost_Per_Annum) +
(a.X * b3.Cost_Per_Annum)
from TableA a
inner join TableB b1 on a.ID = b1.Par_ID and b1.ID = 1
inner join TableB b2 on a.ID = b2.Par_ID and b2.ID = 2
inner join TableB b3 on a.ID = b3.Par_ID and b3.ID = 3
[/code]

rajesh_
Starting Member

8 Posts

Posted - 2007-06-11 : 23:10:34

this is not dynamic khtan. You are taking ID values as 1,2,3. What if i have many records?.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 23:14:04

quote:
Originally posted by rajesh_

this is not dynamic khtan. You are taking ID values as 1,2,3. What if i have many records?.

Well you did not say so.

Please state the full requirement, scenario, post more sample data and the expected result

rajesh_
Starting Member

8 Posts

Posted - 2007-06-11 : 23:46:13

Table A
------------

ID(PK) | V | W | X | Final
----------------------
1 | 4 | 5 | 6 | output
2 | 5 | 6 | 7 | output
3 | 4 | 9 | 10| output

Table B
------------

ID | Par_ID (FK) | Cost_Per_Annum
------------------------
1 | 1 | 12000
2 | 1 | 24000
3 | 1 | 14000
4 | 2 | 10000
5 | 2 | 31000
6 | 2 | 42000
7 | 3 | 12000
8 | 3 | 14000
9 | 3 | 12000

Output should be calculated using below the formula .

For par_id = 1
--------------
output = Column V * 1st record of par_id=1 + Column w * 2nd record of par_id=1 + Column X * 3rd record of par_id=1

Same way for par_id=2 and 3 continues

output for (ID =1) = 4* 12000 + 5* 24000 + 6 * 14000
output for (ID =2) = 5* 10000 + 6* 31000 + 7 * 42000
output for (ID =3) = 4* 12000 + 9* 14000 + 10 * 12000

I given total 3 samples. In real time they may be many records.

Thanks khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 00:22:21

[code]
declare @TableA table
(
ID int,
V int,
W int,
X int,
Final int
)
insert into @TableA (ID, V, W, X)
select 1, 4, 5, 6 union all
select 2, 5, 6, 7 union all
select 3, 4, 9, 10

declare @TableB table
(
ID int,
Par_ID int,
Cost_Per_Annum int
)
insert into @TableB
select 1, 1, 12000 union all
select 2, 1, 24000 union all
select 3, 1, 14000 union all
select 4, 2, 10000 union all
select 5, 2, 31000 union all
select 6, 2, 42000 union all
select 7, 3, 12000 union all
select 8, 3, 14000 union all
select 9, 3, 12000

select a.ID, a.V, a.W, a.X,
Final = sum
(
(a.V * case when row = 1 then b.Cost_Per_Annum else 0 end) +
(a.W * case when row = 2 then b.Cost_Per_Annum else 0 end) +
(a.X * case when row = 3 then b.Cost_Per_Annum else 0 end)
)
from @TableA a
inner join
(
select ID,
Par_ID,
row = (select count(*) from @TableB x where x.Par_ID = b.Par_ID and x.ID <= b.ID),
Cost_Per_Annum
from @TableB b
)b on a.ID = b.Par_ID
group by a.ID, a.V, a.W, a.X

/*
ID V W X Final
----------- ----------- ----------- ----------- -----------
1 4 5 6 252000
2 5 6 7 530000
3 4 9 10 294000
*/
[/code]

You should have another column in TableB that identifies which row is for V or W or X.

rajesh_
Starting Member

8 Posts

Posted - 2007-06-12 : 01:45:02

thank you so much khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 01:50:20

Do take not of my last comment. Currently the query is assuming that the rows in TableB are in ascending order for V, W & X. Which may not be valid any more if you add / delete records from the table. With the additional column in TableB to identify the corresponding records for V, W or X, the query can be re-written more efficiently.

rajesh_
Starting Member

8 Posts

Posted - 2007-06-12 : 02:52:26

I got it. I modified query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:36:50

Why did you design Matrix like table structure?
If you have V,W and X columns in TableB, then that would be too simple to get the result you want
It is just multiplying respective columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources