title: Dense_Rank to return the latest date of a child record field
body: <p>I have a record A and a record B the A is the parent and there may be many B records. The B record has a Start Date and an End Date. I would like to do a date formula in a saved search for record A, and return the latest date for the End Date of a child B record.
  Can I use DENSE_RANK for this? or is there a better option?
  Thanks</p>
  
  <p>I've discovered the GREATEST function that seems to work too. My complication now is that I want to compare the two dates but ignore either if they are greater than today. This is what I've got so far, but it's giving me an error.</p>
  
  <pre><code>CASE 
      WHEN GREATEST 
              ({custrecord_es_lease_property_unit.custrecord_es_lease_tena‌​ncy_end_date}) &gt; {today} 
       THEN 0 
       ELSE 
           CASE WHEN GREATEST({custrecord_es_lease_property_unit.custrecord_es_lease_tena‌​ncy_end_date}) &gt; 
           (
                CASE WHEN GREATEST ({custrecord_es_lease_property_unit.custrecord_es_lease_tena‌​ncy_start_date}) &gt; {today}) 
                THEN 0 
                ELSE 
                    GREATEST ({custrecord_es_lease_property_unit.custrecord_es_lease_tena‌​ncy_start_date}) 
          THEN 1 
      ELSE 0 
      END 
   END 
  END
  </code></pre>