User defined function for currency conversions or rate lookups

Connectivity
Enthusiast

User defined function for currency conversions or rate lookups

Has anybody implemented a UDF for currency conversions or rate lookups. What we are looking for is something like this:

Function ExchngRT(TargetCurrency, StandardCurrency, Date)
with TargetCurrency, StandardCurrency, Date as inputs
and
the output being ExchangeRate.
If StandardCurrency to TargetCurrency data is not available then it should check recursively for the last date where it is available.

Any other similar solution also will do because we can customize to our requirement.

Thanks and
2 REPLIES
Enthusiast

Re: User defined function for currency conversions or rate lookups

You probably could make use of a look up table .........

To give a sample ..

CREATE TABLE CrcyConv
(
TargetCrcy CHAR(2),
StandardCrcy CHAR(2),
End_Date DATE,
ExchngRate FLOAT
)
PRIMARY INDEX (TargetCrcy, StandardCrcy)
;

insert records such that the latest is always having end_date 9999-12-31 (pretty much the norm)

And use an SQL something like this to retrieve the exchange rate.

SELECT ExchngRate
FROM CrcyConv C1
WHERE TargetCrcy = 'TR'
AND StandardCrcy = 'SC'
AND End_Date IN
(
SELECT MIN(End_Date)
FROM CrcyConv C2
WHERE
C2.TargetCrcy = C1.TargetCrcy
AND C2.StandardCrcy = C2.StandardCrcy
AND C2.End_Date > '2006-08-31'
) ;

You can customize it for your requirements.
Enthusiast

Re: User defined function for currency conversions or rate lookups

Thank you Joe.