Interval Match

[racer25]

Hi

I believe the answer is IntervalMatch and possibly left join? But I have not been able to get working hence reaching out for help.

My code is below; The Inline table is not the full table the CURR is real.

Bassically I have a billing table with multi currency information and then an FX Rates table which stores all rates with To and From.

I need to get the FX Rate joined into the Billing Table so I can perform calculations.

I'd appreciate any help.

Thanks,

Rob

BILLING:
LOAD * INLINE [
    Currency, DATE, Invoice, Amount
    EUR, 01/10/2016, 1, 100
    EUR, 15/11/2016, 2, 200,
    GBP, 08/12/2016, 3, 250
];




OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle;Persist Security Info=True;User ID=;Data Source=scsdowp0;Extended Properties=""] (XPassword is C);


TEMPCURR:
LOAD "CURR_CODE",
    "CURR_NAME",
    "BASE_CURR_CODE",
    "CURR_DATE",
    "CURR_EOM_RATE_FC_BASE"
    where BASE_CURR_CODE ='USD'
    ;
SQL SELECT "CURR_CODE",
    "CURR_NAME",
    "BASE_CURR_CODE",
    "CURR_DATE",
     "CURR_EOM_RATE_FC_BASE"
FROM SDO."AON_CURR_MONTHLY_EXCH_RATE_SDO"
;


NoConcatenate
CURR:
LOAD
  CURR_CODE as Currency,
  CURR_NAME as CurrencyName,
  BASE_CURR_CODE as Base_Currency,
  CURR_EOM_RATE_FC_BASE as Rate,
  CURR_DATE as CurrTo,
  previous(CURR_DATE) as CurrFrom
Resident TEMPCURR
Order by CURR_CODE, CURR_DATE;


Drop Table TEMPCURR;

[racer25]

Thanks Rasly - Different to Anna's but equally corrrect.

I will test both with 1000's of invoices and see which is most efficient.

Appreciate your time

[Kushal_Chawda]

In below example, you have two records for,CurrFrom &CurrTo, so on 26/04/2016 which Rate should be consider?

Base_Currency	Currency	CurrencyName	Rate	CurrFrom	CurrTo	AllDates
USD	TZS	TANZANIAN SHILLING	0.000454	26/03/2016	26/04/2016	26/04/2016
USD	TZS	TANZANIAN SHILLING	0.000456	26/04/2016	26/05/2016	26/04/2016

[kusumanchir]

Hi Robert,

Thank you.

In my code, I created a key using 'Currency and Date' in order to link to Billing table. I forgot to use Autonumber() which might increase performance. Could you please add and try it.

Regards,

Rasly.K

[racer25]

Thanks Kushal - I was using Previous to get the Previous FX Date but based on your feedback I have taken CurrFrom + 1