title: What is the best way to code this correlated query? (Select Prices that may contain NULL following a Priority)
body: <p>Lets imagine you have a portfolio to value containing 4 assets that needs to be priced. The SourceID dictates the priority that the PriceSource should be given and the lowest SourceID should be retrieved. Where no price is available from the EODPrice table, then the AverageBookCost should be retrieved (which resides in a different Table). I am using SQL Server 2005.</p>
  
  <p>As an example, lets imagine that EODPrice table has the following data ('TEST004' is missing):</p>
  
  <pre><code>SourceID    Date                   Ticker     Price
  0           2011-08-02 00:00:00    TEST001    104.50
  1           2011-08-01 00:00:00    TEST001    100.00
  1           2011-08-02 00:00:00    TEST001    105.00
  1           2011-08-04 00:00:00    TEST001    115.00
  2           2011-08-03 00:00:00    TEST001    109.38
  2           2011-08-04 00:00:00    TEST001    114.24
  1           2011-08-01 00:00:00    TEST002    9.99
  1           2011-08-02 00:00:00    TEST002    9.89
  1           2011-08-03 00:00:00    TEST002    9.79
  1           2011-08-04 00:00:00    TEST002    9.69
  0           2011-08-03 00:00:00    TEST003    0.42
  2           2011-08-01 00:00:00    TEST003    0.33
  2           2011-08-02 00:00:00    TEST003    0.38
  2           2011-08-03 00:00:00    TEST003    0.28
  2           2011-08-04 00:00:00    TEST003    0.45
  </code></pre>
  
  <p>Lets imagine that we would like to build a Select statement where we retrieve the EODPrice for the following assets ('TEST001','TEST002', 'TEST003', 'TEST004'). Note that 'TEST004' is a new asset that has just hit the Market and no price is available yet in the EODPrice table or the Market.</p>
  
  <p>Further, lets imagine that ALL Tickers for any Date has a non-NULL AverageBookCost field from a RunningTotal Table. (i.e. SELECT AverageBookCost FROM RunningTotal WHERE Ticker = 'TEST004' AND Date = '2011-08-03' would return the value 0.15 say).</p>
  
  <p>How do I build the most efficient 'Correlated' or 'COALESCE / ISNULL' Query:</p>
  
  <pre><code>SELECT Ticker, SourceID, Price
     ???
     WHERE [Date] = '2011-08-03' 
     AND [Ticker] IN ('TEST001','TEST002', 'TEST003' and 'TEST004') 
  </code></pre>
  
  <p>That would return the following Table: (Note as 'TEST004' Price is the AverageBookCost and not in the EODPrice table, then the SourceID is set to NULL to indicate that the Price is from the RunningTotal table:</p>
  
  <pre><code>Ticker   SourceID   Price
  TEST1    2          109.38
  TEST2    1          9.79
  TEST3    0          0.42
  TEST4    NULL       0.15
  </code></pre>
  
  <p>Many thanks,
  Bertie.</p>