So I have a query to get my maximum date and rate, now somehow I need to get the rate into my general ledger entries to output results.
But first it needs to know what rate table to look at ie so if the entity in general ledger field begins with 'C' it looks at chf table, or 'e' then eur table......then it takes the foreign ccy amount in general ledger and converts to max date in ccy table ie with right combination so for example entity begins with E and trans ccy is usd then it should find the maximum date of usd to eur rate.
There is a further complication in that if FCY amount is blank I want it to always take the FCY as gbp and use the amount field to calculate. Ie if entity is beginning with e and FCY amount is blank then take the rate gbp to eur (ie entity begins e so eur ) and calculate it on amount field ( not FCY amount as above ) divided my that rate.
This is what I have so far....please can someone help ? Or is too complex.... Thank you so much.
select t.CurrencyFrom, t.CurrencyTo, t.FXDate, t.FXRate
from _Test t
-- Find max date
(select CurrencyFrom, CurrencyTo, max(FXDate) FXDate from _Test group by CurrencyFrom, CurrencyTo) ma
on ma.CurrencyFrom = t.CurrencyFrom and ma.CurrencyTo = t.CurrencyTo and ma.FXDate = t.FXDate