Your intuition was correct. You cannot declare variables inside parentheses where only a derived table should exist. You also cannot use WITH to begin a CTE inside parenethese where a derived table should exist, but you can achieve what you want with some simple re-arranging of your SQL.

Try this:

DECLARE @year AS CHAR(4);

SET @year = ( SELECT DATEPART(yy, GETDATE()));

DECLARE @firstDay DATETIME;

SET @firstDay = CAST(@year + '0101' AS DATETIME);

WITH dayCTE

AS ( SELECT DATEADD(dd, ROW_NUMBER() OVER ( ORDER BY name ) - 1, @firstDay) AS yearday

FROM master.dbo.spt_values

),

weekCTE

AS ( SELECT yearday,

DATEPART(WW, yearday) AS weekno

FROM dayCTE

WHERE YEAR(yearday) = @year

)

SELECT *

FROM ( SELECT A.WeekStarting,

A.kWh / CASE WHEN B.[AP7 Production] = 0 THEN 1

ELSE B.[AP7 Production]

END AS [kWh per Tonne]

FROM ( SELECT MIN(DATEADD(day, DATEDIFF(day, '19000101', t_stamp) / 7 * 7, '19000101')) AS WeekStarting,

MAX(AP7_main_incomer_kwh) - MIN(AP7_main_incomer_kwh) AS kWh

FROM Ignition.dbo.Burton_Latimer_kWh

WHERE AP7_main_incomer_kwh != 0

AND DATEPART(yy, t_stamp) = ( SELECT DATEPART(yy, GETDATE())

)--Only do current year

AND DATEDIFF(day, '19000101', t_stamp) / 7 > 5901 -- miss out first EVER week of dodgy data as its of no use

GROUP BY DATEDIFF(day, '19000101', t_stamp) / 7

) A

JOIN ( SELECT MIN(DATEADD(day, DATEDIFF(day, '19000101', datecreated) / 7 * 7, '19000101')) AS WeekStarting,

SUM(CASE WHEN Plant = 'AP7' THEN Totalweight

ELSE 0

END) * 1000 AS [AP7 Production]

FROM IgnitionSSISControl.dbo.SSIS_ProductionDetail

WHERE DATEPART(yy, datecreated) = ( SELECT DATEPART(yy, GETDATE())

)--Only do current year

AND DATEDIFF(day, '19000101', datecreated) / 7 > 5901 -- miss out first EVER week of dodgy data as its of no use

GROUP BY DATEDIFF(day, '19000101', datecreated) / 7

) B ON A.WeekStarting = B.WeekStarting

) g --order by A.WeekStarting

JOIN ( SELECT @year AS [YEAR],

weekno AS WEEKNUMBER,

MIN(yearday) AS STARTDATE,

MAX(yearday) AS ENDDATE

FROM weekCTE

GROUP BY weekno

) h --ORDER BY weekno

ON g.Weekstarting = h.Startdate;