Off the top of my head, perhaps a group by SKU, taking the maximum date,
and a where clause on the date? Would the where clause take effect
before the max function?

-----Original Message-----
From: Bob Murdoch [mailto:mailgroups@...]
Sent: Friday, January 07, 2005 2:24 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Looking for ideas - price change history


Rick,

> -----Original Message-----
> From: Rick DeBay [mailto:rdebay@...]
> Sent: Friday, January 07, 2005 2:06 PM
>
> > 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.

I think that works, but only when looking for a single drug (or sku in
my case). How do you handle the situation where you need to report on
all drugs during a specific time frame?

tia,

Bob M..




Yahoo! Groups Links