title: Mysql replace column value with other column value
body: <p>I have 2 tables:</p>
  
  <pre><code>table: transaction:
  ====================
  id  billed_date   amount
   1  2016-09-30      5
   2  2016-10-04      15
   3  2016-10-06      10
  
  table: report_date
  ====================
  transaction_id    report_date
        1            2016-10-01
  </code></pre>
  
  <p><strong>I want:</strong> </p>
  
  <ul>
  <li>Create a report which sum all transactions's amount in October 2016</li>
  <li>Base on report date, not billed date</li>
  <li>When report date is not set, it's base on billed_date</li>
  <li>In above example, I want result is 30 (not 25)</li>
  </ul>
  
  <p><strong>Then I write:</strong></p>
  
  <p>The First:</p>
  
  <pre><code>SELECT 
     sum(t.amount), 
     CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS new_date
  FROM 
     transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
  WHERE new_date BETWEEN '2016-10-01' AND '2016-10-30'
  </code></pre>
  
  <p>The Second:</p>
  
  <pre><code>SELECT sum(amount) FROM 
   (SELECT t.amount,
      CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS date
      FROM transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
   ) t
  WHERE t.date BETWEEN '2016-10-01' AND '2016-10-30'
  </code></pre>
  
  <p><strong>Result:</strong></p>
  
  <p>The First: </p>
  
  <ul>
  <li>Unknown column 'new_date' in 'where clause'</li>
  <li>If I replace 'new_date' by 'date': result = 25 (exclude id=1)</li>
  </ul>
  
  <p>The Second:</p>
  
  <ul>
  <li>result = 30 => Correct, but in my case, when transaction table have about 30k records, the process is too slow.</li>
  </ul>
  
  <p>Anybody can help me?</p>