title: Merging Two Tables and Calculating Amount (Currency Conversion)
body: <p>I have table which contains the currency exchange rates and another one with converted amounts.</p>
  
  <p>Currency Table
  This table will have the daily rates of the currency. For the ease of mapping I have only included rates for one date.</p>
  
  <pre><code>Branch  Code    Rate       Date
  A        0         1    30/10/2019
  A        1       200    30/10/2019
  A        2       300    30/10/2019
  B        0         1    30/10/2019
  B        1        10    30/10/2019
  B        2        30    30/10/2019
  </code></pre>
  
  <p>Converted Amounts Table
  This table will have the daily exchange details. For the ease of mapping I have only included transactions for one date.</p>
  
  <pre><code>Branch  Code     Amt      Date
  A         0     1000    30/10/2019
  A         1     2000    30/10/2019
  A         2     3000    30/10/2019
  A         2     4000    30/10/2019
  A         2     5000    30/10/2019
  A         0     6000    30/10/2019
  B         0     7000    30/10/2019
  B         0     8000    30/10/2019
  B         0     9000    30/10/2019
  B         2    10000    30/10/2019
  </code></pre>
  
  <p>Resultant Table</p>
  
  <pre><code>Branch  Code   Date        Amt   Branch Code   Rate   Total
  A          0   30/10/2019  1000   A      0       1     1000
  A          1   30/10/2019  2000   A      1     200   400000
  A          2   30/10/2019  3000   A      2     300   900000
  A          2   30/10/2019  4000   A      2     300  1200000
  A          2   30/10/2019  5000   A      2     300  1500000
  A          0   30/10/2019  6000   A      1       1     6000
  B          0   30/10/2019  7000   A      1     200  1400000
  B          0   30/10/2019  8000   A      1     200  1600000
  B          0   30/10/2019  9000   A      1     200  1800000
  B          2   30/10/2019 10000   A      2     300  3000000
  </code></pre>
  
  <p>I could only get the following resultant table.</p>
  
  <pre><code>Branch  Code    Date     Amt    Branch  Code    Rate    Total
  A        0  30/10/2019  1000       A      0       1      1000
  A        1  30/10/2019  2000       A      1     200    400000
  A        2  30/10/2019  3000       A      2     300    900000
  A        2  30/10/2019  4000       A      2     300   1200000
  A        2  30/10/2019  5000       A      2     300   1500000
  A        0  30/10/2019  6000       A      1       1      6000
  B        0  30/10/2019  7000       A      1       1      7000
  B        0  30/10/2019  8000       A      1       1      8000
  B        0  30/10/2019  9000       A      1       1      9000
  B        2  30/10/2019  10000      A      2     300   3000000
  </code></pre>
  
  <p>Query I use.</p>
  
  <pre><code>SELECT 
    * 
  FROM 
    converted_amounts_table t1 
    LEFT OUTER JOIN (
      SELECT 
        (
          CASE WHEN code = '0' 
          AND branch = 'B' THEN '1' ELSE code END
        ) AS new_code, 
        branch, 
        date, 
        rate 
      FROM 
        currency_table
    ) t2 ON (
      t1.date = t2.date 
      AND t1.code = t2.new_code
    ) 
  WHERE 
    t1.date &gt;= '01-Jan-2019' 
    AND t1.date &lt;= '30-Sep-2019'
  </code></pre>