question

How can I join my CTE table from a derived table? as I am getting an "Incorrect Syntax error near )" Error in join expression, unable to parse query (SELECT TOP (100) PERCENT ItemSpecs_1.ItemID, SUM(SalesOrderDetails_1.QtyOrdered * ItemSpecFullStruc_1.TotalQtyPerRoot) AS SalesQTY FROM dbo.SalesOrderDetails AS SalesOrderDetails_1 INNER JOIN dbo.ItemSpecFullStruc AS ItemSpecFullStruc_1 ON SalesOrderDetails_1.ItemSpecID = ItemSpecFullStruc_1.RootItemSpecID INNER JOIN dbo.ItemSpecs AS ItemSpecs_1 ON ItemSpecFullStruc_1.ChildItemSpecID = ItemSpecs_1.ItemSpecID INNER JOIN dbo.SalesOrder ON SalesOrderDetails_1.SalesOrderID = dbo.SalesOrder.SalesOrderID WHERE (SalesOrderDetails_1.SOStatus < 6) AND (dbo.SalesOrder.EntryDate > GETDATE() - 84) GROUP BY ItemSpecs_1.ItemID ORDER BY ItemSpecs_1.ItemID) AS [12WeekSales] ON dbo.Items.ItemID = [12WeekSales].ItemID LEFT OUTER JOIN and this is my CTE table ;WITH mytemp AS (SELECT TOP (100) PERCENT ItemSpecs_2.ItemNo, WorkOrderDetails.QtyComplete * ItemSpecFullStruc_2.TotalQtyPerRoot AS QtyComp FROM dbo.WorkOrderDetails AS WorkOrderDetails INNER JOIN dbo.ItemSpecFullStruc AS ItemSpecFullStruc_2 ON ItemSpecFullStruc_2.RootItemSpecID = WorkOrderDetails.ItemSpecID INNER JOIN dbo.ItemSpecs AS ItemSpecs_2 ON ItemSpecs_2.ItemSpecID = ItemSpecFullStruc_2.ChildItemSpecID INNER JOIN dbo.WorkOrder AS WorkOrder_1 ON WorkOrder_1.WorkOrderID = WorkOrderDetails.WorkOrderID LEFT OUTER JOIN dbo.ToBeScheduled_Completed ON WorkOrder_1.WorkOrderID = dbo.ToBeScheduled_Completed.WorkOrderID WHERE (WorkOrderDetails.Completed = 1) AND (WorkOrderDetails.CompDate > GETDATE() - 42) GROUP BY ItemSpecs_2.ItemNo, WorkOrderDetails.QtyOrdered, ItemSpecFullStruc_2.TotalQtyPerRoot, WorkOrderDetails.[LineNo], WorkOrderDetails.QtyComplete, WorkOrderDetails.CompDate, WorkOrderDetails.QtyComplete * ItemSpecFullStruc_2.TotalQtyPerRoot) SELECT ItemNo, SUM(QtyComp) AS WorkQTY FROM mytemp AS WOSold GROUP BY ItemNo ) AS WOSold ON dbo.Items.ItemID = WOSold.ItemID LEFT OUTER JOIN ItemNo QtyCom 7441 9