Sql Server CTE "multi- part identifier could not be bound."

Question

My last question on this same cte query was answered so quickly, I thought I'd bounce this next one off you sql gurus. If I could go over my basic logic, then show my code and syntax error, any help would be greatly appreciated..

I have three tables for a stock trading system: a symbols table: as the name suggests it's a list of ticker symbols, a daily pricing/ volume table: again, as- described, and each record has a date field and symbol field as well as the pricing info, and lastly a trading dates table: the reference for all trading dates in our query.

I'd like to return a recordset with two fields: a symbol and a date. The pair represents all trading dates and symbols that don't have corresponding pricing/ vol data for that symbol in the pricing volume table. Make sense? On my query, I'm getting the error message: "The multi-part identifier "Symb.Symbol" could not be bound." Here's my cte query:

WITH Symb AS
(
     SELECT Symbol
     FROM tblSymbolsMain
),

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol
     WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),

WideDateRange AS
(
     SELECT TradingDate
     FROM tblTradingDays
     WHERE (TradingDate >= dbo.NextAvailableDataDownloadDateTime()) AND (TradingDate <= dbo.LatestAvailableDataDownloadDateTime())
),

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded)
)

SELECT Symb.Symbol, DatesNeeded.TradingDate
FROM Symb CROSS JOIN DatesNeeded

Answer 1

This:

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol
     WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),

Needs to be this:

DatesNotNeeded AS
(
     SELECT Date
     FROM tblDailyPricingAndVol inner join Symb on
         tblDailyPricingAndVol.Symbol = Symb.Symbol
),

But your query still won't work, since this:

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded)
)

Needs to be this:

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded d where d.Date = wdr.TradingDate)
)

But really, you can do this without CTEs, like this:

select
    sm.Symbol,
    tb.TradingDate
from
    tblSymbolsMain sm
    cross join tblTradingDays tb
    left join tblDailyPricingAndVol dp on
        sm.Symbol = dp.Symbol 
        and tb.TradingDate = dp.Date
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime()
        and dbo.NextAvailableDataDownloadDatetime()
    and dp.Date is null

This query grabs all the symbols from tblSymbolsMain and all the dates between your last and next available dates from tblTradingDays. Then it does a left join on tblDailyPricingAndVol and filters out any row that found a match.

You could also use not exists in lieu of a left join, which I think is a bit clearer, too:

select
    sm.Symbol,
    tb.TradingDate
from
    tblSymbolsMain sm
    cross join tblTradingDays tb
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime() 
        and dbo.NextAvailableDataDownloadDatetime()
    and not exists (
        select
            1
        from
            tblDailyPricingAndVol dp
        where
            dp.Symbol = sm.Symbol
            and dp.Date = tb.TradingDate
    )