title: SQL nested case with value differences between 2 tables
body: <p>I have imported two Excel sheets as tables in Microsoft's SQL Server Management Server 2007, and they are both identical, except for the fact that they are from 2 different dates. </p>
  
  <p>I'm looking to do 2 things that I'm struggling to do:</p>
  
  <ol>
  <li>Calculate the monthly difference between values for the 2 tables which I can do with <code>cast</code>, and </li>
  <li><p>inner join, but I'm not successful in using those values to be able to sum those values with a nested case like so:</p>
  
  <pre><code>SELECT SUM(
         CASE WHEN ID &lt;&gt;'MISSING' THEN 
             CASE WHEN SUM(VALUE)&gt;=0 THEN 
                  SUM(VALUE) 
             ELSE 
                  0 
             END 
         END) 
  </code></pre></li>
  </ol>
  
  <p>I've tried many different ways, but one of the main errors I get is:</p>
  
  <blockquote>
    <p>Cannot perform an aggregate function on an expression containing an aggregate or a subquery.</p>
  </blockquote>
  
  <p>The data would like so:  </p>
  
  <p><strong>dbo.Table1</strong>   </p>
  
  <pre><code>date(dd/mm/yy) | name |   id    | value
  ---------------+------+---------+-------
      1/1/14     |  A   | MISSING |  56    
      1/1/14     |  A   | MISSING |  -1    
      1/1/14     |  B   | YES     |  56       
      1/1/14     |  B   | YES     |  -1   
  </code></pre>
  
  <p><strong>dbo.Table2</strong>   </p>
  
  <pre><code>date(dd/mm/yy) | name |   id    | value
  ---------------+------+---------+-------
      1/2/14     |  A   | MISSING |  24    
      1/2/14     |  A   | MISSING | -11    
      1/2/14     |  B   | YES     |  24       
      1/2/14     |  B   | YES     | -11   
  </code></pre>