2 WITH statements

[pvong]

Doing this in SQL 2005. How do you connect 2 WITH statements. I'm trying
to use (;) or (,) and neither is working. What am I doig wrong. I'm just
tyring to get the second WITH table to show up.

Thanks from a newbie

*******************************************************************
WITH MtdCount(TraderID, MTD)
AS

(SELECT Commission.TraderID, COUNT(Trades.Broker) AS MTD FROM Trades
RIGHT OUTER JOIN Commission ON Trades.Broker = Commission.TraderID WHERE
(Trades.TradeDate >= DATEADD(mm, DATEDIFF(mm, 0, @TradeDate), 0)) AND
(Trades.TradeDate <= @TradeDate) GROUP BY Commission.TraderID);

WITH LastYrComm (TraderID, TotalLastYrComm)
AS
(SELECT Traders.TraderID, SUM(Trades.TotalComm) AS TotalLastYrComm
FROM Trades INNER JOIN
Traders ON Trades.Broker = Traders.TraderID
WHERE (Traders.EquityBond = 'Equity') AND (Trades.TradeDate >=
DATEADD(YYYY, DATEDIFF(YYYY, 0, CURRENT_TIMESTAMP) - 1, 0)) AND
(Trades.TradeDate < DATEADD(YYYY, DATEDIFF(YYYY, 0,
CURRENT_TIMESTAMP), 0))
GROUP BY Traders.TraderID)

SELECT Commission_1.TraderID,
Traders.FirmName,
SUM(Trades_1.TotalComm) AS YTDComm,
COALESCE (MtdCount_1.MTD, 0) AS MTD

FROM Commission AS Commission_1
INNER JOIN
Traders ON Commission_1.TraderID = Traders.TraderID
LEFT OUTER JOIN
MtdCount AS MtdCount_1 ON Commission_1.TraderID = MtdCount_1.TraderID
LEFT OUTER JOIN
LastYrComm AS LastYrComm_1 ON Commission_1.TraderID = LastYrComm_1.TraderID
LEFT OUTER JOIN
Trades AS Trades_1 ON Commission_1.TraderID = Trades_1.Broker AND
Trades_1.TradeDate >= DATEADD(yy, DATEDIFF(yy, 0, @TradeDate), 0) AND
Trades_1.TradeDate <= @TradeDate

GROUP BY Commission_1.TraderID, Traders.FirmName, Commission_1.SortOrder,
COALESCE (MtdCount_1.MTD, 0)
ORDER BY Commission_1.SortOrder

[Plamen Ratchev]

You use a comma but only the first CTE needs the WITH keyword:

WITH MtdCount(TraderID, MTD) AS
(SELECT ...),
LastYrComm (TraderID, TotalLastYrComm) AS
(SELECT ...)
SELECT ...
FROM ...

--
Plamen Ratchev
http://www.SQLStudio.com

[pvong]

Thank you. That was perfect!!!

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:lsqdnTcjv7tfV5fW...@speakeasy.net...