List:General Discussion« Previous MessageNext Message »
From:mos Date:September 28 2006 4:36am
Subject:Need to find last price and date product was sold
View as plain text  
This should be easy but I can't find a way of doing it in 1 step.

I have a Trans table like:

Product_Code: X(10)
Date_Sold: Date
Price_Sold: Float

Now there will be 1 row for each Product_Code, Date combination. So over 
the past year a product_code could have over 300 rows, one row for each day 
it was sold. There are thousands of products.

What I need to do is find the last price_sold for each product_code. Not 
all products are sold each day so a product might not have been sold for weeks.

The only solution I've found is to do:

drop table if exists CurrentPrices;
create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), 
-1.0 Price_Sold from Trans group by Prod_Code;
alter table CurrentPrices add index ix_ProdCode (Prod_Code);
update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and 
T.Date_Sold=CP.Date_Sold;

Is there a way to shorten this? It may take 2-3 minutes to execute. I don't 
really need a new table as long as I get the Prod_Code and the last Date_Sold.

TIA
Mike
Thread
Need to find last price and date product was soldmos28 Sep
  • Re: Need to find last price and date product was soldDouglas Sims28 Sep
  • Re: Need to find last price and date product was soldjoao28 Sep
    • Re: Need to find last price and date product was soldmos28 Sep
  • Re: Need to find last price and date product was soldJonathan Mangin28 Sep
  • Re: Need to find last price and date product was soldPeter Brawley28 Sep
    • Re: Need to find last price and date product was soldDouglas Sims28 Sep