This:
DatesNotNeeded AS
(
SELECT Date
FROM tblDailyPricingAndVol
WHERE (tblDailyPricingAndVol.Symbol = Symb.Symbol)
),
需要是这样的:
DatesNotNeeded AS
(
SELECT Date
FROM tblDailyPricingAndVol inner join Symb on
tblDailyPricingAndVol.Symbol = Symb.Symbol
),
但您的查询仍然不起作用,因为:
DatesNeeded AS
(
SELECT TradingDate
FROM WideDateRange wdr
WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded)
)
需要是这样的:
DatesNeeded AS
(
SELECT TradingDate
FROM WideDateRange wdr
WHERE NOT EXISTS (SELECT * FROM DatesNotNeeded d where d.Date = wdr.TradingDate)
)
但实际上,您可以在没有 CTE 的情况下做到这一点,如下所示:
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
此查询获取所有符号tblSymbolsMain
以及您最后一个可用日期和下一个可用日期之间的所有日期tblTradingDays
。然后它做了一个left join
on tblDailyPricingAndVol
并过滤掉找到匹配项的任何行。
你也可以使用not exists
代替left join
,我认为也更清楚一点:
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
)