(reposted from Firebird-general per admins advice)

I'm having a mental block solving this problem -

I have a table that contains information regarding the price of a
piece of merchandise, and a table that contains the inventory of a
warehouse:

create table SKU (
sku_nbr integer not null,
cost numeric(9,2),
retail numeric(9,2),
...);

create table INVENTORY (
sku_nbr integer not null,
warehouse_nbr integer not null,
receipt_date date default 'today',
...);


The price of a sku may change a couple of times per month. I had
created a table quite a while ago that was populated via an after
update trigger on the sku table, which simply wrote the old values of
the row to a table, along with the timestamp of when the change
occurred:

create table SKU_CHANGE (
sku_nbr integer not null,
change_date date,
cost numeric(9,2),
retail numeric(9,2),
...);


My task is to now create a query that will, for each piece of
inventory, return the cost and retail of the piece as of the day of
receipt - not the current values in the SKU table. This is
complicated by the fact that the sku_change table only contains the
old values upon change, so it's not a simple matter of just joining to
that table on SKU_CHANGE.CHANGE_DATE = INVENTORY.RECEIPT_DATE.


I'm looking for general principles on tackling this kind of thing. I
am free to rearchitect the design of the SKU_CHANGE table to
accomplish this. I know I had seen something similar posted about
this type of topic a few years ago, but can't seem to find the magic
words to successfully search for that thread.


Thanks in advance for sharing your thoughts.

Bob M..