venki123
New User
Joined: 05 Feb 2008 Posts: 1 Location: pune
|
|
|
|
Table A
order# receipt line# cost-code Cost-rate
234 1 1 xx .22
234 1 1 xy .11
234 1 1 xz .33
234 1 2 xx .22
234 1 2 xz .33
234 2 1 xx .22
234 3 1 xx .22
234 3 2 xx .22
234 3 2 xy .11
234 3 4 xz .33
Table B
order# receipt line# line-qty
234 1 1 10
234 1 2 6
234 2 1 10
234 3 1 9
234 3 2 12
234 3 4 15
I have TWO tables A & B with above colums.
Table 1:- Order contains receipt, receipt may contain multiple lines. Again line will contains different associated cost-codes.
Table 2:- each line contains quantity
I want to calcalualte the one recipt value of for all cost codes associated with it.
Eg:-
________1(10)--xx,xy,xz
|
1 receipt__________|
|
|________2(06)--xx,xz.
receipt 1 has 2 lines(of qunatity 10 & 6 respectively ) & each line has differnt cost codes.
Suppose I want receipt 1 value in the order 234 for cost-code xx.
receipt 1 value @ cost code xx :- (10X.22) + (6X.22)
{line 1 calculation ) + {line 2 calculation }
same for all
receipt 1 value @ cost code xx :- (10X.22) + (6X.22) = A
receipt 1 value @ cost code xy :- (10X.33) + (6X.33) = B
receipt 1 value @ cost code xz :- (10X.11) = C
nOW Total receipt value is A+B+c.
So that I can calculate all the recips value one by one.
Can you give me an idea how can i write a querry for this. Please note this should be in PERFORM loop.
Thanks for your help.
|
|