title: MAX With CASE expression
body: <p>I have the below table:</p>
  
  <p>Criteria:</p>
  
  <p>Take EndDate of latest row per ID
  If EndDate is null then  Active
  If Enddate is not null then Closed</p>
  
  <p>TableA</p>
  
  <pre><code>StartDate       EndDate            ID
    04/01/2017      06/30/2017        1   
    10/01/2017                        1
    11/01/2017      05/01/2017        2
  </code></pre>
  
  <p>Expected Results:</p>
  
  <pre><code>StartDate       EndDate            ID    CurrentStatus
    04/01/2017      06/30/2017        1       Active
    10/01/2017                        1       Active
    11/01/2017      11/02/2017        2       Closed
  </code></pre>
  
  <p>Sql Code:</p>
  
  <pre><code>SELECT * 
  MAX(CASE WHEN CCP.ENDDATE IS NULL THEN 'Active' END)  
           OVER (PARTITION BY CCP.ID) AS CURRENT_STATUS
  FROM TABLEA CCP
  </code></pre>
  
  <p>Can I add an Else condition for "Closed" customers?</p>