2 WITH statements

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 


0
pvong
11/23/2009 5:34:11 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
720 Views

Similar Articles

[PageSpeed] 0

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
0
Plamen
11/23/2009 5:47:54 PM
Thank you.  That was perfect!!!



"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:lsqdnTcjv7tfV5fWnZ2dnUVZ_vdi4p2d@speakeasy.net...
> 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 


0
pvong
11/23/2009 5:58:04 PM
Reply:

Similar Artilces: