title: SQL: Track changes on MAX value over time-frame
body: <p>We have a so called evaluation process for new products which is fairly complex. A product is evaluated for different Regions and applications. Afer each evalution step the product gets a new Result score. this score (between 0 to 10) says how far the product is in the process. For each step it stays the same or is increased but never decreased and uneven numbers mark products that failed the evaluation. The highest score is called the products Status.</p>
  
  <p>I no want to Select all products that had an even status (2,4,8,10) at startDate (including that status) and the status at endDate of the time frame. </p>
  
  <p>(I also want to select all new products that entered the proccess in that time frame but I think that can be done easly in a second statement.)</p>
  
  <p>The issue I have is how to include both initial status in the output. Here is my SQL Statement:</p>
  
  <pre><code>SELECT 
    MyTable.product_id, 
    MyTable.REGION, 
    MyTable.SEGMENT,
    Max(MyTable.result) AS NEW_STATUS
    FROM 
       MyTable INNER JOIN (
       SELECT
        product_id, 
        REGION, 
        SEGMENT, 
        Max(result) AS INITIAL_STATUS
      FROM
        MyTable
      WHERE
        DATE &lt;= to_date(:startDate)
      GROUP BY
        product_id, REGION, SEGMENT
      HAVING
        Max(result) IN(2,4,8,10)
     ) initial_status ON MyTable.product_id = initial_status.product_id    
    WHERE
      MyTable.DATE &lt;= to_date(:endDate)
    GROUP BY
      MyTable.product_id, 
      MyTable.REGION, 
      MyTable.SEGMENT;
  </code></pre>
  
  <p>How can I include initial_status in the output without affecting max/group by?
  (is oracle, but I'm no expert so maybe some oracle specific stuff can help)</p>
  
  <p>EDIT:</p>
  
  <p>the data is in a 1 to many relationship. 1 product, many evaluations. each evaluation has an Region, segment, result and evaluation_date (plus other data not relevant here). denormalized here some example data:</p>
  
  <pre><code>product_id    Region    Segment    Result    date
      1           US         AB         2    20.05.2012
      1           EU         TS         4    13.06.2012
      1           US         AB         4    01.09.2012
    234           US         AB         2    09.09.2012
  </code></pre>
  
  <p>Expected output for above sample with date Range from 26.08.2012 to 21.09.2012:</p>
  
  <pre><code>product_id    Region    Segment    Initial_Status    New_Status
      1            US        AB             2              4
      1            EU        TS             4              4 (this did not change)
    234            US        AB           (null)           2 ( new entry)
  </code></pre>
  
  <p>I know my current SQL can't achieve that. especially displaying the values that are new.</p>