I have a nested select structure that works until I try to do the 3rd and last wrap and for the life of me I cannot see where the issue is.
This is my code
The top and bottom lines are commented out as that is where the issue lies....
-- SELECT w3.[Country], w3.[Product Category], w3.[Speed Range A4], w3.[QUnits], w3.[TUnits], w3.[Year], w3.[Quarter], w3.[Quarter+1], w3.[Quarter+2], w3.[Quarter+3], w3.[Quarter+4], w3.[Quarter+5] FROM (
SELECT w2.[Country], w2.[Product Category], w2.[Speed Range A4], w2.[QUnits], w2.[TUnits], w2.[Year], w2.[Quarter], w2.[Quarter+1], w2.[Quarter+2], w2.[Quarter+3], w2.[Quarter+4], w2.[Quarter+5], w2.[Y+1], w2.[Units], w2.[Units1], w2.[Y+2], d.[Units], SUM([QUnits]/[TUnits]d.[Units]) AS [Units2], w2.[Y+3], w2.[Y+4], w2.[Y+5] FROM (
SELECT w1.[Country], w1.[Product Category], w1.[Speed Range A4], w1.[QUnits], w1.[TUnits], w1.[Year], w1.[Quarter], w1.[Quarter+1], w1.[Quarter+2], w1.[Quarter+3], w1.[Quarter+4], w1.[Quarter+5], w1.[Y+1], c.[Units], w1.[Y+2], SUM([QUnits]/[TUnits][Units]) AS [Units1], w1.[Y+3], w1.[Y+4], w1.[Y+5] FROM (
SELECT a.[Country], a.[Product Category], a.[Speed Range A4], SUM(a.[Units]) AS [QUnits], b.[TUnits], LEFT(a.[Quarter],4) AS [Year], a.[Quarter], CAST(LEFT(a.[Quarter],4)+1 AS nvarchar(4))+RIGHT(a.[Quarter],2) AS [Quarter+1], CAST(LEFT(a.[Quarter],4)+2 AS nvarchar(4))+RIGHT(a.[Quarter],2) AS [Quarter+2], CAST(LEFT(a.[Quarter],4)+3 AS nvarchar(4))+RIGHT(a.[Quarter],2) AS [Quarter+3], CAST(LEFT(a.[Quarter],4)+4 AS nvarchar(4))+RIGHT(a.[Quarter],2) AS [Quarter+4], CAST(LEFT(a.[Quarter],4)+5 AS nvarchar(4))+RIGHT(a.[Quarter],2) AS [Quarter+5], LEFT(a.[Quarter],4)+1 AS [Y+1], LEFT(a.[Quarter],4)+2 AS [Y+2], LEFT(a.[Quarter],4)+3 AS [Y+3], LEFT(a.[Quarter],4)+4 AS [Y+4], LEFT(a.[Quarter],4)+5 AS [Y+5]
/* INTO #OTHERSQ*/
FROM [marketdata_eeb].[dbo].[IDC_FORECAST_RAW] a
LEFT JOIN #OTHERST b ON a.[Country] = b.[Country] AND a.[Product Category] = b.[Product Category] AND a.[Speed Range A4] = b. [Speed Range A4] AND a.[Year] = b.[Year]
WHERE a.[Speed Range A4] NOT LIKE 'Color InkJet%' AND a.[Quarter] in (SELECT [Quarter] FROM #FCQ4)
GROUP BY a.[Country], a.[Product Category], a.[Speed Range A4], a.[Quarter], b.[TUnits]
) w1
LEFT JOIN #FCYO c ON w1.[Country] = c.[Country] AND w1.[Product Category] = c.[Product Category] AND w1.[Speed Range A4] = c. [Speed Range A4] AND w1.[Y+1] = c.[Year]
GROUP BY w1.[Country], w1.[Product Category], w1.[Speed Range A4], w1.[QUnits], w1.[TUnits], w1.[Year], w1.[Quarter], w1.[Quarter+1], w1.[Quarter+2], w1.[Quarter+3], w1.[Quarter+4], w1.[Quarter+5], w1.[Y+1], c.[Units], w1.[Y+2], w1.[Y+3], w1.[Y+4], w1.[Y+5]
) w2
LEFT JOIN #FCYO d ON w2.[Country] = d.[Country] AND w2.[Product Category] = d.[Product Category] AND w2.[Speed Range A4] = d. [Speed Range A4] AND w2.[Y+2] = d.[Year]
GROUP BY w2.[Country], w2.[Product Category], w2.[Speed Range A4], w2.[QUnits], w2.[TUnits], w2.[Year], w2.[Quarter], w2.[Quarter+1], w2.[Quarter+2], w2.[Quarter+3], w2.[Quarter+4], w2.[Quarter+5], w2.[Y+1], w2.[Units], w2.[Units1], w2.[Y+2], d.[Units], w2.[Y+3], w2.[Y+4], w2.[Y+5]
-- ) w3