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

This is how we do it. Order by the date descending, use your where, and
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
given date.

-----Original Message-----
From: Bob Murdoch [mailto:mailgroups@...]
Sent: Thursday, January 06, 2005 8:12 PM
To: firebird-support@yahoogroups.com
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