Matching dates in queries (1 Viewer)

[red386]

Hi

I have two tables, one is an extract from an FMIS that shows the actual exchange rates that occurred when transactions are entered, the other table shows sales according to the sales system (a separate system).

The FMIS data may refer to many days' sales transactions that have been periodically aggregated into one entry and posted against one date in the FMIS (ie the dates will likely not match that in the sales systems one to one).
The sales system, however, has the actual date of the transaction for each transaction.

I want to be able to find out what exchange rate was applied for each sales transaction when it was posted to the FMIS. Problem is, I cannot join the tables by date - as the FMIS does not have all individual transaction dates - just the aggregated posting dates.

Table one - SalesTable has the following fields and example data:

[InputDate], [CountryID], [LclCurrency], [SalesAmounti]
10/02/08,123,USD,15000
11/02/08,123,USD,2000
12/02/08,123,USD,150
13/02/08,123,USD,5000

Table two - FMISTable has the following fields and example data:

[EntryDate], [CountryID], [Currency], [ActualExchangeRate]
10/02/08,123,USD,15000
15/02/08,123,USD,7150

I want the query to return the FMIS actual exchange rate for the 10/02/08 for the 10/02/08 sales transaction, however I want it to return the FMIS actual exchange rate of the 15/02/08 for sales transactions that occurred on 11/02,12/02 and 13/02 as, given the gap, the assumption is that they have been recognised on the next recognition date of 15/02/08.

Any ideas?

Thanks

Rebecca

 

[Jon K]

See the two queries in the database. One is updatable. The other is non-updatable but runs a little faster.


Note:
Running domain aggregate functions or subqueries in a correlated way in queries are inefficient and will take time if the tables are large.

Since the ActualExchangeRates will not change once they are recorded, if the tables are large, you will find it more efficient to input them directly into the SalesTable at the time they are obtained than extract them by means of queries.

.