Subject Looking for ideas - price change history
Author Bob Murdoch
(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

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

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

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..