Raymond,

Yes, that would be the easiest idea. However, I cannot guarantee
that the SKU table has been updated on a given day prior to the
addition of that days INVENTORY data (the system is populated by
importing data files from another system).

Thank you,

Bob M..

> -----Original Message-----
> From: Raymond Kennington [mailto:raymondwk@...]
> Sent: Tuesday, January 04, 2005 12:10 PM
> To: Firebird-general@yahoogroups.com
> Subject: Re: [Firebird-general] Looking for ideas - price
> change history
>
>
> Current values are better stored with the inventory item.
>
> Use the current price table for lookup, but store the value,
> not the link, as the current value is not relevant once the
> value has changed.
>
> Raymond.
>
> 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.
> >
> > Thanks in advance for sharing your thoughts.
> >
> > Bob M..
> >
> >
> >
> > Community email addresses:
> > Post message: Firebird-general@yahoogroups.com
> > Subscribe: Firebird-general-subscribe@yahoogroups.com
> > Unsubscribe: Firebird-general-unsubscribe@yahoogroups.com
> > List owner: Firebird-general-owner@yahoogroups.com
> >
> > Shortcut URL to this page:
> > http://www.yahoogroups.com/community/Firebird-general
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> Community email addresses:
> Post message: Firebird-general@yahoogroups.com
> Subscribe: Firebird-general-subscribe@yahoogroups.com
> Unsubscribe: Firebird-general-unsubscribe@yahoogroups.com
> List owner: Firebird-general-owner@yahoogroups.com
>
> Shortcut URL to this page:
> http://www.yahoogroups.com/community/Firebird-general
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>