At 08:20 AM 6/01/2005 -0500, you wrote:

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

I don't record "changes" as such. But, to me, a SKU isn't about pricing,
it's about things like size, colour, packs and so on - attributes that
don't vary.

Inventory is about what you've actually got in stock - what's there, what's
reserved, what's expected, etc. You don't show anything about quantities,
yet recording the received date presumes that you're interested at least in
arrival order and *might* be concerned to keep stock quantity data by date
as well.

If you're using a dynamic costing/pricing system, the simplest thing to do
is to have a 1:Many relationship between the SKU and the pricing. It can
often be more complicated: different costings/pricings depending on
supplier, different pricing for promotional campaigns, etc. But, taking
the simple situation:

create table SKU (
sku_nbr integer not null,
list_price_id integer, <- stores sku_pricing_id of one pricing record
...);

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

create table SKU_PRICING (
sku_pricing_id integer not null
sku_nbr integer not null,
receipt_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.

Notice that I replace those values with a link to a single SKU_PRICING
record, to remove redundancy. This assumes that you're taking the list
prices from the latest SKU_PRICING record, but that might or might not be
what's required. For example, many inventory systems require the list
prices to be calculated as rolling averages on a periodic basis, or when
triggered by an inward goods receipt, or when there is no "R" in the month,
or whatever abstruse algorithm the accountant might dream up. Promotional
or fire-sale overrides might justify the redundancy, too.

Your query for matching up inventory with pricing at receipt doesn't need
the SKU table, at least on information supplied. A simple join between
Inventory and SKU_Pricing, matching sku_number and receipt_date, will get it.

If you happen to want the list prices as well, just join in the SKU table
with a re-entrant join on SKU_Pricing, or use a correlated subquery, to
pick up the prices from the targeted record.

Eventually, presumably, you will churn the pricing records for exhausted
stock out to history, once the system doesn't need them any more for
returns, creative stocktakes, etc.

./hb