regarding the conversion of currency from INR to n e other currency

Database
Enthusiast

regarding the conversion of currency from INR to n e other currency

i have one table with amount in Indian currency and currency_code is INR and having start_date and end_date column.and i have another table which have details about the conversion rate,source_code,target_code,start_date and end_date.

so for example source_code is INR and target as Dollor and conversion_rate is 50.

we need to consider the start and end date too becoz the conversion rate keep on changing as per date.

do suggest the possible solution

2 REPLIES
Enthusiast

Re: regarding the conversion of currency from INR to n e other currency

Hi,

How about joining on currency_code, start_date and end_date with source_code,start_date and end_date of  2nd table and get the calculation ?

Please do verify your data also, or get the design properly so that it macthes the dates properly and get the conversion rates for those dates.

Cheers,

Raja

Teradata Employee

Re: regarding the conversion of currency from INR to n e other currency

Hi Peiter, 

when you join two tables, both of them having start_date and end_date, then the join condition for those columns (in addition to currency_code, etc.) should be:

      table1.start_date <= table2.end_date

and table2.start_date <= table1.end_date

With these conditions, you get the rows that have "intersecting" time periods.    

And the new periods are:

case when table1.start_date <= table2.start_date then table2.start_date else table1.start_date end as NEW_START_DATE, /* greatest (...) */

case when table1.end_date <= table2.end_date then table1.end_date else table2.end_date end as NEW_END_DATE, /* least (...) */


Should be tested, of course.

Regards,

Vlad.