argh...date problems

gabby_moore · #1 (**permalink**) 07-19-04, 13:23

Hi,

I have a quick SQL query that i'm having problems with. Basically i have a product table and a price history table which have a common product_code field. The price history table contains the history of price changes for all products, so there will be one or more rows in it for each product_code (each having an effective_date field which determines when the price becomes/became effective).

I need to query the price table for a list of products and return one row per product containing the product information and current price information for that product

My level of SQL is pretty basic and i can't figure out how to restrict the rows returned from the price table to just the current prices (i.e. the max effective_date before the current date)

LKBrwn_DBA · #2 (**permalink**) 07-19-04, 14:51

In Oracle:

Code:

select * from product_table p, product_hist h
where h.prod_cd = p.prod_cd
   and h.eff_date = (
         select MAX(eff_date) from product_hist d
         where d.prod_cd = p.prod_cd);

gabby_moore · #3 (**permalink**) 07-20-04, 09:22

cheers for that

Internet Services

Web Development

Digital Marketing

Consumer Tech