title: Update row based on other rows sql
body: <p>I have a table:</p>
  
  <p>Table Details with Data:<br/></p>
  
  <pre><code>ID ReferenceNumber Status AuditPeriod  CustomerStatus
  1       112        Expired 1/1/2016      
  2       113        Pending 1/1/2016     
  3       114        Expired 1/1/2016     
  4       115        Pending 2/1/2016     
  5       116        Approved 2/1/2016
  6       117        Pending  2/1/2016
  7       118        Approved 3/1/2016
  8       119       Approved  3/1/2016 
  </code></pre>
  
  <p>Now I need to Update CustomerStatus column by grouping with auditperiod based on 
  Status Column.</p>
  
  <blockquote>
    <ul>
    <li><p>Condition1: If atleast <strong>one status is Expired in auditperiod</strong> then <strong>CustomerStatus is NotCompleted</strong>.</p></li>
    <li><p>Condition2: If atleast <strong>one status is Pending in auditperiod</strong> then <strong>customerstatus is Pending</strong> .</p></li>
    <li><p>Condition3: If <strong>all status are approved</strong> then <strong>customerStatus is Completed</strong>.<br/></p></li>
    </ul>
  </blockquote>
  
  <p>Final Output should be as follows:</p>
  
  <pre><code>ID ReferenceNumber Status AuditPeriod  CustomerStatus
  1       112        Expired 1/1/2016    Not Completed
  2       113        Pending 1/1/2016    Not Completed 
  3       114        Expired 1/1/2016    Not Completed 
  4       115        Pending 2/1/2016     Pending
  5       116        Approved 2/1/2016    Pending
  6       117        Pending  2/1/2016    Pending 
  7       118        Approved 3/1/2016    Completed 
  8       119       Approved  3/1/2016    Completed.
  </code></pre>
  
  <p>Please let me know whether this can be done WITH OUT exporting into another temp table (Using single update)</p>
  
  <p>Please let me know if any information is needed from my side</p>