transactions & multicurrency

Question

transactions & multicurrency

paton

SSC Enthusiast

Points: 154
More actions
June 29, 2004 at 10:42 am

#161749

Hi, I posted this question in another server but i am having email problems,
I need to write a query based on transactions using multicurrency.
This is the stripped down version of the two tables involved:
Transaction Table
TranID Date Cost
1 1/1/2004 100.00
2 2/3/2004 100.00
3 4/2/2004 100.00
4 5/3/2004 100.00
5 6/3/2004 100.00
6 6/3/2004 100.00
Exchange Rate Table
ID Date Exchange
1 1/2/2003 1.23
2 4/4/2004 1.25
3 5/4/2004 1.55
4 6/1/2004 1.33
so , I need a query that will convert the cost in the
transaction table into the appropiate currency
based on the exchange rate .
It should check the exchange rate table to compare the dates.
Result needed.
TranID Date Cost
1 1/1/2004 123.00
2 2/3/2004 123.00
3 4/2/2004 123.00
4 5/3/2004 125.00
5 6/3/2004 155.00
6 6/3/2004 133.00
these numbers might be off a bit, there are just used to ilustrate
what's needed.
any ideas, TIA.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

Well, the basic query structure is fairly straightforward. Your big problem is the first transaction, which has a date before the first entry in your Exchange Rate table.

The correct way to handle that is to have an initial entry. However, you *can* do this without it, as long as you make the assumption that any transaction before the first exchange rate table record uses the first record's value.

If there is an initial record, then:

SELECT TranID, Date, (Cost * (Select Exchange From ExchangeRate e
                              WHERE e.date = 
                                  (Select max(e1.date) FROM ExchangeRate e1
                                   WHERE e1.date <= t.Date) 
                              ) 
                      ) As Cost
FROM Transactions t

It is more complex without the initial date

paton SSC Enthusiast Points: 154 More actions · Answer 2

that seems to work and I can definitely make that assumption.

thanks

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

If you use that query a lot, you might want to turn it into a view, make a cross join. You can then treat it like a normal table. and select as much as you want from it.

Julian Kuiters
juliankuiters.id.au