I am working on a query where I need to convert some amounts based on date, as (amount*conversion_factor).
If conversion factor is not available for any date than I should pick the factor from last available date.
Eg. For dates 01-01-2022, 02-01-2022, 03-01-2022. If conversion factor is not available for 02 and 03 than factor of 01 should be used.
I have attached the DDL/DML statements here for reference. In attached example, conversion factor for 2022-05-06 and 2022-05-07 are not available thus we are supposed to pick the last available factor from date 2022-05-05.
I tried using Ranking functions, LAG function, <= in Left Join and few other things as well but no success as of now.
Any help will be much appreciated.