CODEWITHSUNDEEP
sql-serversql-server-2008common-table-expression
StatsViaCsh
StatsViaCsh

Reputation: 2640

Sql Server CTE syntax error... help, please?

just getting my feet wet with sql server express. I have what would be a fairly complicated couple of subqueries, and I simplified it/ them with a cte. I get the error "Incorrect syntax near 'DatesNotNeeded.' Any takers? Thanks in advance...

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
     WHERE NOT EXISTS (DatesNotNeeded)
),

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

Upvotes: 1

Views: 476

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Your query for DatesNeeded is flawed.

 SELECT TradingDate
 FROM WideDateRange
 WHERE NOT EXISTS (DatesNotNeeded)

It should be something more like:

 SELECT TradingDate
 FROM WideDateRange wdr
 WHERE NOT EXISTS (SELECT 1 FROM DatesNotNeeded WHERE Date = wdr.TradingDate)

Upvotes: 1

Related Questions