title: SQL optimization Case statement
body: <p>I believe I can optimize this sql statement by using a case statement for the Left Outer Joins.</p>
  
  <p>But I have been having hard time setting up the cases, one for summing up the code types AB,CD and another for All the rest.  </p>
  
  <p>Appreciate any help or tips you can give me on this.  </p>
  
  <pre><code>update billing set payments = isnull(bd1.amount, payments)
  , payments = case 
         when payments is null then 0 
         else payments 
       end
  , charges = case 
          when bd2.amount is not null then charges 
          when charges is null then 0 
          else charges 
        end
  , balance = round(charges + isnull(bd1.amount, bi.payments), 2) 
  from billing bi 
  
  left outer join (select inv, round(sum(bd1.bal), 2) amount 
                  from "bill" bd1 
                  where code_type = 'AB'
                   or code_type = 'CD' 
                  group by inv) bd1 
                  on bd1.inv = bi.inv 
  left outer join (select invoice, round(sum(bd2.bal), 2) amount 
                  from "bill" bd2 
                  where code_type &lt;&gt; 'AB'
                   and code_type &lt;&gt; 'CD' 
                  group by inv) bd2 
                  on bd2.inv = bi.inv;
  </code></pre>