CURREGITEMS

CURREGITEMS is the name of the table that contains historical data regarding representative rates of different currencies. It has three fields of interest, namely CURRENCY, CURDATE and EXCHANGE: these are self-explanatory. The table is populated automatically upon the entry of new data into the connected CURRENCIES table. So if one runs the report Financials > Maintenance of Financials > Exchange Rates > Daily Exchange Rates (CURRENCIES) for USD, one gets the following data:

Date	Rate
30/11/23	3.714
01/12/23	3.739
04/12/23	3.708
05/12/23	3.728

Thus if one wants the dollar representative rate for an invoice dated 04/12/23, simple code to do so would be 

SELECT EXCHANGE FROM CURREGITEMS
WHERE CURRENCY = -2
AND CURDATE = 04/12/23;

Of course, one would use parameters for CURRENCY and CURDATE; The result for this query would be 3.708. 

But what happens if one wants the exchange rate for Sunday, 03/12/23? One must remember that the Bank of Israel does not publish representative exchange rates for Saturdays and Sundays, and indeed the above table does not have an entry for 03/12/23. The way that I was taught (or taught myself) many years ago when first working with Priority is that when there is no value in CURREGITEMS, one uses the value of the EXCHANGE field in the CURRENCIES table which is the most up-to-date value. As a result, I have written in many reports or procedures expressions such as

SELECT (:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE :
CURRENCIES.EXCHANGE)) INTO :EXCH
FROM CURRENCIES, CURREGITEMS ?
WHERE CURRENCIES.CURRENCY = CURREGITEMS.CURRENCY
AND CURRENCIES.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;

A problem with this code is if one is showing data from several months ago; the representative rate for the dollar might have been 3.5 but now it's much higher. I thought that I would have to write a loop to get the representative rate of the date previous to the desired rate, something like

LABEL 10;
:EXCH = 0.0;
(:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE)) INTO :EXCH
FROM CURREGITEMS 
WHERE CURREGITEMS.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;
:DATE = :DATE - 24:00;
LOOP 10 WHERE :EXCH = 0.0;

But it transpires that this is totally unnecessary! There is a value for 03/12/23 in CURREGITEMS - it's the same value as for 01/12/23, or in other words, the last known representative rate. There's no need for left joins between CURRENCIES and CURREGITEMS.

I have no idea whether this has always been the case and that the queries that I wrote in 2007 were misguided even then, or whether the method of inserting tuples in CURREGITEMS has changed in the passing years. I suspect that there has been no change and that I have been perpetuating a misunderstanding for the last 17 years. I learn something new every day.

APPENDUM: the original code with a left join between CURRENCIES and CURREGITEMS is necessary when the given date is in the future. In this case, there definitely will not be an appropriate entry in CURREGITEMS and so it's 'correct' to use the value from CURRENCIES.