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

Question

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.

Answer 1

I dont know the structure of your tables, but from what I understand you can do something like this:

select pkInvoice, InvoiceAmount, ExchangeRate
from Invoices i1
 left outer join 
  (select pkInvoice, max(EffectiveDate) as effDate
   from Invoices i2 inner join ExchangeRates ex2
    on i.InvoiceDate > ex.EffectiveDate
   group by pkInvoice) rates on i1.pkInvoice = rates.pkInvoice
 left outer join ExchangeRates ex1 on rates.effDate = ex1.EffectiveDate

Here, basically you fetch the effective date for the exchange rate that has the latest effective date, but earlier than the invoice date, for each invoice row.

Then join that effective date with the exchange table to get the rate, and join that information with your invoices table...