Subject Re: Looking for ideas - price change history
Author tigereye_philip

Sorry to reply to myself (and from yahoo-groups, as it lets me see
the reply tree,) but I don't think I ever saw a reason my solution to
your problem wouldn't work, though it is incomplete -- you later
mentioned you're getting notifications as price changes happen, so
you'd need some mechanism/trigger to calculate and insert only the
relative diff value, not the raw 'new' value. The sum/group_by works
well for multiple SKU items at a time, too... I seem to remember that
being a question you asked at some point.

If only for my own education, is there some problem I'm not seeing
related to my solution? Notifications being sent out of order, etc.?

Thanks and good luck,

--- In, "unordained"
<unordained_00@c...> wrote:
> This would be a great way to use the accounting method and
do 'changes' at points in time.
> --- ------------ ----------
> 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