title: SQL calculated column with case statement and table join
body: <p>Okay, I've done some web digging and cannot seem to find how to make this situation work or even if it is possible.</p>
  
  <p>I am using SQL Server 2008 and have a calculated column where i am trying to figure some values regarding foreign currency exchange rates.</p>
  
  <p>Although one single table might be easier in the long run, I would like to use the correct solution which involves two different tables. </p>
  
  <p>table <code>t1</code> houses a log of transactions. table <code>t2</code> houses basic information for a given currency. The two tables can be joined using the <code>currencyCode</code> attribute.</p>
  
  <p>The sticky part comes with currency dependent calculations. Presently the calculations only differ for <code>CR1</code> <code>CR2</code> <code>CR3</code> or <code>CR4</code> but in the future as more currencies are added, the potential exists for the new currencies to differ. So ideally, a new row would be added to <code>t2</code> and a bit flag (<code>flagCurrencyCalc</code>) set that specifies the alternate calculation be used or not used.</p>
  
  <p>So the present formula looks something like this:</p>
  
  <pre><code>(case when [currencyCode]='CR1' OR [currencyCode]='CR2' OR [currencyCode]='CR3' OR [currencyCode]='CR4' then (formula1) else (formula2) end)
  </code></pre>
  
  <p>As you can see, i would have to manually go in and alter the formual by adding another OR statement with the new currencyCode.</p>
  
  <p>Would it be possible to do something along the line of:</p>
  
  <p><code>(case when t2.flagCurrencyCalc=True</code> for a given currency code in a record found in t1 <code>then (formula1) else (formula2) end)</code></p>
  
  <p>???</p>