MimUK
asked on
Trigger to Calculate Currency Conversion
I have a table called MarketPotential
mapo_marketpotentialid mapo_prodavl mapo_prodavl_CID mapo_prodavlsterling
1 12 3
2 11 3
3 100 1
I need that when a value is entered into the mapo_prodavl and the mapo_prodavl_CID that it references another table called Currency
curr_currencyid curr_rate
1 1.5438
2 1.6534
3 1.0000
4 193.008
and takes the mapo_prodavl looks up the mapo_prodavl_CID value in the Currency table (curr_currencyid) and divides the value by the curr_rate and populates the mapo_prodavlsterling with that value.
In the example above mapo_potentialmarketid 1 and 2 will divide by 1.000 thus equalling the same value, but line 3 would be 100 divided by 1.5438 and populating the mapo_prodavlsterling as 64.77523
Phheww.
Maximum Points awarded
Cheers
Mim
mapo_marketpotentialid mapo_prodavl mapo_prodavl_CID mapo_prodavlsterling
1 12 3
2 11 3
3 100 1
I need that when a value is entered into the mapo_prodavl and the mapo_prodavl_CID that it references another table called Currency
curr_currencyid curr_rate
1 1.5438
2 1.6534
3 1.0000
4 193.008
and takes the mapo_prodavl looks up the mapo_prodavl_CID value in the Currency table (curr_currencyid) and divides the value by the curr_rate and populates the mapo_prodavlsterling with that value.
In the example above mapo_potentialmarketid 1 and 2 will divide by 1.000 thus equalling the same value, but line 3 would be 100 divided by 1.5438 and populating the mapo_prodavlsterling as 64.77523
Phheww.
Maximum Points awarded
Cheers
Mim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, just a minor amendment
set mapo_prodavlsterling = t1.mapo_prodavl / t3.curr_rate
from MarketPotential t1
inner join inserted t2 on t1.mapo_prodavl_CID = t2.mapo_prodavl_CID
and t1.mapo_prodavl = t2.mapo_prodavl
and t1.mapo_marketpotentialid = t2.mapo_marketpotentialid
inner join Currency t3 on t1.mapo_prodavl_CID = t3.curr_currencyid
set mapo_prodavlsterling = t1.mapo_prodavl / t3.curr_rate
from MarketPotential t1
inner join inserted t2 on t1.mapo_prodavl_CID = t2.mapo_prodavl_CID
and t1.mapo_prodavl = t2.mapo_prodavl
and t1.mapo_marketpotentialid = t2.mapo_marketpotentialid
inner join Currency t3 on t1.mapo_prodavl_CID = t3.curr_currencyid
ASKER