title: Calculating MAX(DATE) for Value Groups Where Values Go Back and Forth
body: <p>I have another challenge that I am trying to resolve but unable to get the solution yet. Here is the scenario. Pardon the formatting if it messes up at the time of posting. </p>
  
  <pre><code>  ACCT_NUM  CERT_ID  Code    Date    Desired Output 
        A        1      10   1/1/2007    1/1/2008 
        A        1      10   1/1/2008    1/1/2008 
        A        1      20   1/1/2009    1/1/2010 
        A        1      20   1/1/2010    1/1/2010 
        A        1      10   1/1/2011    1/1/2012 
        A        1      10   1/1/2012    1/1/2012 
        A        2      20   1/1/2007    1/1/2008 
        A        2      20   1/1/2008    1/1/2008 
        A        2      10   1/1/2009    1/1/2010 
        A        2      10   1/1/2010    1/1/2010 
        A        2      30   1/1/2011    1/1/2011 
        A        2      10   1/1/2012    1/1/2013 
        A        2      10   1/1/2013    1/1/2013 
  </code></pre>
  
  <p>As you can see, I need to do a MAX on the date based on each group of code values (apart from ACCT_NUM and CERT_ID) before the value changes. If the same value repeats, I need to a MAX of the data again for that group separately. For example, for CERT_ID of '1', I cannot group all four rows of Code 10 to get a MAX date of 1/1/2012. I need to get the MAX for the first two rows and then another MAX for the next two rows separately since there is another code in between. I am trying to accomplish this in Cognos Framework Manager. </p>
  
  <p>Gurus, please advise.</p>