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.
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' ) ;