I need some help to calculate exchange rate. I have two tables with each of the programs and their corresponding amount and commit date in each currency. A program can have a different currency for each table. On the other hand I have a table with currency source, currency destination, date and exchange rate. I need to add to each of the tables the amount and the commit according to the currency filtered by the user (by default EURO). The program table must calculate the exchange rate with the date of each of the programs, while the pipeline to calculate the commit must be done with the date of the reporting date filtered by the user (by default today ()). I was thinking of duplicating the exchange rate table and join them to each of the tables but in my real app each program can have up to 10 different currency types but the model would be complicated and the execution time would increase.
The tables should come out with these values without any filter type indicated (default today () and EUROS)