Adding mutiple columns

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.

hi Rajesh,

Try this out

Code Snippet

declare @.TableA table(ID int,V int,W int, X int )
declare @.TableB table(ID int,Par_ID int,Cost_Per_Annum int)
insert into @.TableA select 1,4,5,6

insert into @.TableB select 1,1,12000
insert into @.TableB select 2,1,24000
insert into @.TableB select 3,1,14000

select a.*,
a.v * (
select top 1 v.Cost_Per_Annum from
( select top 1 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc )+
a.W *(
select top 1 v.Cost_Per_Annum from
( select top 2 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc)+
a.X *(
select top 1 v.Cost_Per_Annum from
( select top 3 Cost_Per_Annum,ID from @.TableB where Par_ID=a.ID
) v order by id desc ) Final
from @.TableA a

|||

This should get you started.

Code Snippet

declare @.t1 table (i int, v int, w int, x int)
insert @.t1 select 1,4,5,6

declare @.t2 table (iii int, i int, c int)
insert @.t2 select 1,1,12000
union all select 2,1,24000
union all select 3,1,14000

select a.i,sum(vv*c) as [output]
from(
select * ,case ii when 'v' then 1 when 'w' then 2 when 'x' then 3 end as iii
from (select i, v,w,x from @.t1) as p
unpivot
(vv for ii in(v,w,x)) as unpvt
)a
join @.t2 b on a.i=b.i and a.iii=b.iii
group by a.i

|||Output printing wrong values. Your query value is 202000
Actual output is 252000.

I think problem in 16th line. Can you please check.

thanks|||Please copy the query block as it is and check once more , iam getting the output 252000 with the same query.|||i am getting 202000. Please refer following link for screen shot .

http://picavo.com//images/371729err.JPG

Raj
|||It is really strange, still working for me. Which version of sql server r u using?|||I am using sqlserver 2000.

|||I had checked it on MSDE with SP3 and Sql server Express edition, working on both.|||can i report this problem to Microsoft bug team?
|||

Try:

Code Snippet

createtable dbo.t1 (

ID int,

V int,

W int,

X int

)

go

insertinto dbo.t1 values(1, 4, 5, 6)

insertinto dbo.t1 values(2, 7, 8, 9)

go

createtable dbo.t2 (

ID int,

Par_ID int,

Cost_Per_Annum int

)

go

insertinto dbo.t2 values(1, 1, 12000)

insertinto dbo.t2 values(2, 1, 24000)

insertinto dbo.t2 values(3, 1, 14000)

insertinto dbo.t2 values(4, 2, 10000)

insertinto dbo.t2 values(5, 2, 20000)

insertinto dbo.t2 values(6, 2, 30000)

go

select

x.ID,

(V * Cost_Per_Annum_V)+(W * Cost_Per_Annum_W)+(X * Cost_Per_Annum_X)as [output]

from

dbo.t1 as x

innerjoin

(

select

c.Par_ID,

max(casewhen d.rn = 1 then c.Cost_Per_Annum end)as Cost_Per_Annum_V,

max(casewhen d.rn = 2 then c.Cost_Per_Annum end)as Cost_Per_Annum_W,

max(casewhen d.rn = 3 then c.Cost_Per_Annum end)as Cost_Per_Annum_X

from

dbo.t2 as c

innerjoin

(

select

a.Par_ID,

a.ID,

count(*)as rn

from

dbo.t2 as a

innerjoin

dbo.t2 as b

on a.Par_ID = b.Par_ID

and a.ID >= b.ID

groupby

a.Par_ID,

a.ID

)as d

on c.Par_ID = d.Par_ID

and c.ID = d.ID

groupby

c.Par_ID

)as y

on x.ID = y.Par_ID

go

droptable dbo.t1, dbo.t2

go

AMB