Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: argh...date problems

    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)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    cheers for that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •