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
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 (...) */