Sql Server Cte "multi- Part Identifier Could Not Be Bound."

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 synt

Solution 1:

This:

DatesNotNeeded AS
(
     SELECTDateFROM tblDailyPricingAndVol
     WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),

Needs to be this:

DatesNotNeeded AS
(
     SELECTDateFROM 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
     WHERENOTEXISTS (SELECT*FROM DatesNotNeeded)
)

Needs to be this:

DatesNeeded AS
(
     SELECT TradingDate
     FROM WideDateRange wdr
     WHERENOT 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
    crossjoin tblTradingDays tb
    leftjoin tblDailyPricingAndVol dp on
        sm.Symbol = dp.Symbol 
        and tb.TradingDate = dp.Date
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime()
        and dbo.NextAvailableDataDownloadDatetime()
    and dp.Date isnull

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
    crossjoin tblTradingDays tb
where
    tb.TradingDate between 
        dbo.LatestAvailableDataDownloadDateTime() 
        and dbo.NextAvailableDataDownloadDatetime()
    andnotexists (
        select1from
            tblDailyPricingAndVol dp
        where
            dp.Symbol = sm.Symbol
            and dp.Date = tb.TradingDate
    )