What is the best way to code this correlated query? (Select Prices that may contain NULL following a Priority)

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 sho开发者_开发问答uld be retrieved (which resides in a different Table). I am using SQL Server 2005.

As an example, lets imagine that EODPrice table has the following data ('TEST004' is missing):

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

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.

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).

How do I build the most efficient 'Correlated' or 'COALESCE / ISNULL' Query:

SELECT Ticker, SourceID, Price
   ???
   WHERE [Date] = '2011-08-03' 
   AND [Ticker] IN ('TEST001','TEST002', 'TEST003' and 'TEST004') 

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:

Ticker   SourceID   Price
TEST1    2          109.38
TEST2    1          9.79
TEST3    0          0.42
TEST4    NULL       0.15

Many thanks, Bertie.

I most likely overcomplicated things but this might get you started.

In a nutshell, the reasoning goes like this

• In a subselect, SELECT all tickers for given date with their lowest SourceID.
• JOIN the results of this subselect back with the original table. This step allows the price to be retrieved for each ticker with given date and lowest SourceID.
• FULL OUTER JOIN the previous result with the average book cost. This step adds to each row the average price for that ticker and adds rows for tickers that don't have a record returned from Pricetable.
• SELECT from these results the Price if available, otherwise select from the appended columns the average book cost.

SQL Statement

SELECT  [Ticker] = ISNULL(pt.Ticker, pt_avg.Ticker)
        , [SourceID] = pt.SourceID
        , [Price] = ISNULL(pt.Price, pt_avg.AverageBookCost)
FROM    EODPriceTable pt 
        INNER JOIN (
          SELECT  SourceID = MIN(SourceID), Ticker, Date
          FROM    EODPriceTable
          WHERE   Date = '2011-08-03 00:00:00'
          GROUP BY
                  Ticker, Date
         ) pt_min ON  pt_min.SourceID = pt.SourceID 
                      AND pt_min.Ticker = pt.Ticker
                      AND pt_min.Date = pt.Date
         FULL OUTER JOIN (
            SELECT  Ticker, AverageBookCost
            FROM    RunningTable
         ) pt_avg ON pt_avg.Ticker = pt.Ticker
WHERE  ISNULL(pt.Ticker, pt_avg.Ticker) IN ('TEST001', 'TEST002', 'TEST003', 'TEST004')

Test script

;WITH EODPriceTable (SourceID, Date, Ticker, Price) AS (
  SELECT 0, '2011-08-02 00:00:00', 'TEST001', 104.50
  UNION ALL SELECT 1, '2011-08-01 00:00:00', 'TEST001', 100.00
  UNION ALL SELECT 1, '2011-08-02 00:00:00', 'TEST001', 105.00
  UNION ALL SELECT 1, '2011-08-04 00:00:00', 'TEST001', 115.00
  UNION ALL SELECT 2, '2011-08-03 00:00:00', 'TEST001', 109.38
  UNION ALL SELECT 2, '2011-08-04 00:00:00', 'TEST001', 114.24
  UNION ALL SELECT 1, '2011-08-01 00:00:00', 'TEST002', 9.99
  UNION ALL SELECT 1, '2011-08-02 00:00:00', 'TEST002', 9.89
  UNION ALL SELECT 1, '2011-08-03 00:00:00', 'TEST002', 9.79
  UNION ALL SELECT 1, '2011-08-04 00:00:00', 'TEST002', 9.69
  UNION ALL SELECT 0, '2011-08-03 00:00:00', 'TEST003', 0.42
  UNION ALL SELECT 2, '2011-08-01 00:00:00', 'TEST003', 0.33
  UNION ALL SELECT 2, '2011-08-02 00:00:00', 'TEST003', 0.38
  UNION ALL SELECT 2, '2011-08-03 00:00:00', 'TEST003', 0.28
  UNION ALL SELECT 2, '2011-08-04 00:00:00', 'TEST003', 0.45
)
, RunningTable (Ticker, AverageBookCost) AS (
  SELECT 'TEST004', 0.15
  UNION ALL SELECT 'TEST003', 0.09
)
SELECT  [Ticker] = ISNULL(pt.Ticker, pt_avg.Ticker)
        , [SourceID] = pt.SourceID
        , [Price] = ISNULL(pt.Price, pt_avg.AverageBookCost)
FROM    EODPriceTable pt 
        INNER JOIN (
          SELECT  SourceID = MIN(SourceID), Ticker, Date
          FROM    EODPriceTable
          WHERE   Date = '2011-08-03 00:00:00'
          GROUP BY
                  Ticker, Date
         ) pt_min ON  pt_min.SourceID = pt.SourceID 
                      AND pt_min.Ticker = pt.Ticker
                      AND pt_min.Date = pt.Date
         FULL OUTER JOIN (
            SELECT  Ticker, AverageBookCost
            FROM    RunningTable
         ) pt_avg ON pt_avg.Ticker = pt.Ticker
WHERE  ISNULL(pt.Ticker, pt_avg.Ticker) IN ('TEST001', 'TEST002', 'TEST003', 'TEST004')