At 08:12 PM 6/01/2005 -0500, you wrote:

> > 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.
>
>Ah, there's the rub. This assumes that there will be a price change
>every day.

No, it assumes that your logic (trigger or application) will write a a
corresponding SKU_Pricing record each time a unique combination of
sku_number and date_received enters the Inventory table.

This in turn presumes that your structure provides a way to constrain
uniqueness on sku_number and date_received. That in turn presumes that a
DATE type was the right choice (it will *never* happen that you get more
than one inwards goods movement per sku_number per day).

>I guess the crux of the matter is this:
>
>Assuming SKU_PRICING table has the following rows:
>
>SKU PRICE_DATE PRICE
>--- ----------- -----
>123 1-Dec-2004 1.50
>123 9-Dec-2004 3.50
>123 7-Jan-2005 2.25
>
>Given a sku of '123', and a receipt date of '6-Jan-2005', the
>following would not work:
>
>select
> i.receipt_date, s.price
>from
> inventory i
> join sku_pricing s on (s.sku = i.sku and s.price_date =
>i.receipt_date)
>where
> i.sku = 123
>
>as there is no record in SKU_PRICING with 6-Jan-2005.

Why not? Is the date_received attribute of Inventory meaningful or
not? How could you have an SKU_Pricing record that didn't correspond to an
Inventory item?

>Likewise this won't work either
>
>select
> i.receipt_date, s.price
>from
> inventory i
> join sku_pricing s on (s.sku = i.sku and s.price_date <=
>i.receipt_date)
>where
> i.sku = 123
>
>Doing a join on SKU_PRICING.PRICE_DATE <= INVENTORY.RECEIPT_DATE this
>way won't work, because we will get two rows in the results because of
>the two earlier dates in SKU_PRICING.

The logic of your query causes this problem. If you want to match up an
Inventory record with a specific price record, why would you target a range
of dates?

>A sticky one indeed.

A sticky "something".... to extract information from the data you store,
you have to have the right data stored. IOW, to derive facts from the
abstract stuff that's in your tables, you have to design tables that store
data which are actually capable of being used to derive those facts.

It's an inviolable rule that, in order to design a solution for a
requirement, you have to understand the requirement first.

To give a very simple example: let's suppose that your Inventory table is
keyed on sku_number and date_received, which is a DATE type. This will
constrain the inventory table so that, if two or more shipments for the
same sku_number come into the store on the same day, only the first can be
accepted. Oh dear. What are you going to do about the later
shipments? Ignore them? post-date them? ask the truck driver to come back
tomorrow?

Of course not. The structure doesn't fit your requirements. You really
needed a TIMESTAMP for that uniqueness constraint.

...and so on...

./hb