Having issues with a CTE in a Nested Join I'm unfortunately dealing with someone else's code here, but needing a little push.

What I have happening is that I have a CTE that is needed to run in a nested join.

...Start of CTE
INNER JOIN (
;WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate &lt; @BegRange 
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate &lt;= @EndRange
GROUP BY StockCode, WeekJobStartDate
)
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) AS WipDet ON InvMaster.StockCode = WipDet.StockCode   ...


I've tried removing the semi-colon in front of the CTE, but then get a syntax error stating it is expecting that the previous statement must be terminated with a semi-colon.

I beating my head against the keyboard on this one.
This sounds like dynamic SQL where you're constructing a query from other subqueries...

The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML.

So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ORDER BY, so that's good.

You could find it useful to make WipDet a CTE too... Like this:

WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate &lt; @BegRange 
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate &lt;= @EndRange
GROUP BY StockCode, WeekJobStartDate
),
WipDet AS (
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) 
SELECT ...
FROM InvMaster
INNER JOIN WipDet 
ON InvMaster.StockCode = WipDet.StockCode