--- In Firebird-general@yahoogroups.com, "Bob Murdoch" wrote:

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

If you changed your SKU_CHANGE table to contain the new values rather
than the old values and wrote after insert and update trigger(s) for
the SKU table to populate this (i.e. so that the SKU_CHANGE table
contained both historic and current data), then you could do something
like

SELECT <something>
FROM SKU_CHANGE SC
JOIN INVENTORY I ON I.sku_nbr = SC.sku_nbr
WHERE SC.change_date <= I.receipt_date
AND NOT EXISTS (SELECT * FROM SKU_CHANGE SC2
WHERE SC2.sku_nbr = SC.sku_nbr
AND SC2.change_date BETWEEN SC.change_date AND I.receipt_date)

or (saying the same with slightly different code, I don't know which
is most efficient)

SELECT <something>
FROM SKU_CHANGE SC
JOIN INVENTORY I ON I.sku_nbr = SC.sku_nbr
WHERE SC.change_date = (select max(sc2.change_date)
FROM SKU_CHANGE SC2
WHERE SC2.sku_nbr = SC.sku_nbr
AND SC2.change_date <= I.receipt_date)

or the third alternative using FIRST (that one I leave for you to
write yourself). I simply do not know which one would be the most
efficient.

As for general principles, I don't know ;o} I just felt like answering
this question as if it was asked in firebird-support. Sorry for being
Off-Topic (for the group, On-Topic for your question) in my answer.

Set