This would be a great way to use the accounting method and do 'changes' at points in time.

> SKU PRICE_DATE PRICE_DIFF
> --- ------------ ----------
> 123 1-Dec-2004 1.50
> 123 9-Dec-2004 2.00
> 123 7-Jan-2005 -1.25

Then you can do the following:

> select
> i.sku, i.receipt_date, sum(s.price_diff)
> from
> inventory i
> join sku_pricing s on (s.sku = i.sku and s.price_date <=
> i.receipt_date)
> where
> i.sku = 123
> group by i.sku, i.receipt_date

At least I think that'd work ... but for our accounting system, it works wonders (except we do the
actual debit/credit columns ... don't ask me why.)

-Philip

---------- Original Message -----------
From: "Bob Murdoch" <mailgroups@...>
To: <firebird-support@yahoogroups.com>
Sent: Thu, 6 Jan 2005 20:12:06 -0500
Subject: RE: [firebird-support] Looking for ideas - price change history

> Helen,
>
> > -----Original Message-----
> > From: Helen Borrie [mailto:helebor@...]
> > Sent: Thursday, January 06, 2005 9:19 AM
> >
> > 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.
>
> Yes, that's a pretty good idea, and will cut down on the duplicated
> information kept in my current SKU_CHANGE table.
>
> > 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.
>
> 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.
>
> 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.
>
> A sticky one indeed.
>
> Thank you for your time,
>
> Bob M..
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------