Creating Date Range from multiple rows inside select function for progress open edge 11

I have a table of exchange rates that includes the date at which it has become effective (effective date). I want to join this information to a table of invoices to calculate the proper exchange rate. The problem is that I need to have a date range that is the derived from the the exchange rate table where the "date-to" field is the "effective-date" from the subsequent row (assuming that we have sorted the table according to the date). I need to be able to derive this value and then join it to the rest of my single query.

The normal means of indexing using Row_Number() or Lag and Lead do not apply here as progress uses a "business logic" that uses sequences. I am not sure how to approach this problem.

If it is any help, we are using apprise ERP and their schema for the progress open edge database.

Would love any help or advice on how to structure this query.

Continue reading...