title: Max(Date) query with another condition
body: <p>my record: </p>
  
  <pre><code>SF_ID    CardID   Status  Received_Date    Meetup_Date
  
  12    1   Yes     2015-01-12       2015-12-03    
  13    1   No      2015-12-01       NULL
  14    1   No      2015-12-01       NULL
  15    2   No      2015-12-02       NULL
  16    2   No      2015-12-02       NULL
  17    3   No      2015-12-01       NULL
  18    4   No      2015-12-06       NULL
  19    5   Yes     2015-11-30       2015-12-01 
  20    5   No      2015-11-30       NULL
  22    5   No      2015-11-30       NULL
  23    7   yes     2015-12-06       2015-12-07 
  </code></pre>
  
  <p>Requirement #1: </p>
  
  <p>all <code>cardID</code> where <code>Received_Date</code> is Minimum and <code>Status</code>
  is <code>No</code> and <code>Top 01 SF_ID</code>. I've tried it in the following way without success:</p>
  
  <pre><code>SELECT CardSFID, 
         CardID, 
         CardSFShortDate 
  FROM   CC_Shortfall AS [data] 
  WHERE  ( CardSFShortDate = (SELECT TOP 1 PERCENT CardSFShortDate 
                              FROM   CC_Shortfall 
                              WHERE  CardID = [data].CardID) ) 
         AND CardSFYesNo = 'No' 
  </code></pre>
  
  <p>Requirement #2: </p>
  
  <p>all <code>cardID</code> Where <code>MeetUp_Date</code> is Maximum and <code>Status</code>
  is <code>Yes</code> and there are not any <code>No Status</code> under <code>CardID</code></p>
  
  <p>i've tried to do it in the following way without succcess:</p>
  
  <pre><code>SELECT CardSFID, 
         CardID, 
         CardSfShortRcvDate 
  FROM   CC_Shortfall AS [data] 
  WHERE  ( CardSfShortRcvDate = (SELECT Max(CardSfShortRcvDate) 
                                 FROM   CC_Shortfall 
                                 WHERE  CardID = [data].CardID) ) 
         AND CardSFYesNo = 'Yes' 
  </code></pre>