|Subject||RE: [firebird-support] Looking for ideas - price change history|
> Doing a join on SKU_PRICING.PRICE_DATE <= INVENTORY.RECEIPT_DATE thisThis is how we do it. Order by the date descending, use your where, and
> way won't work, because we will get two rows in the results because of
> the two earlier dates in SKU_PRICING.
prepend with SELECT FIRST 1.
I have a drugprice table, and push changes to the drugpricehistory
table, which I join with to find historical pricing. This is because
most of the time I want to find the current price, not the price on a
From: Bob Murdoch [mailto:mailgroups@...]
Sent: Thursday, January 06, 2005 8:12 PM
Subject: RE: [firebird-support] Looking for ideas - price change history
> -----Original Message-----Yes, that's a pretty good idea, and will cut down on the duplicated
> 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.
information kept in my current SKU_CHANGE table.
> Your query for matching up inventory with pricing at receiptAh, there's the rub. This assumes that there will be a price change
> 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.
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:
join sku_pricing s on (s.sku = i.sku and s.price_date =
i.sku = 123
as there is no record in SKU_PRICING with 6-Jan-2005.
Likewise this won't work either
join sku_pricing s on (s.sku = i.sku and s.price_date <=
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,
Yahoo! Groups Links