Using SSMS/SQL Server 2008 R2.
I have the following SQL script that uses two CTEs and a PIVOT statement
DECLARE @WEEK_START_SUBTRACTER INT;
SET @WEEK_START_SUBTRACTER = 34;
-- CTE1 SChedule History (FORECASTS) and Date Dimension
WITH ctevh
AS
(
SELECT
dd.WeekOfYear - @WEEK_START_SUBTRACTER AS PlanningWeekNo
,sh.FORECAST_CALLS
,sh.CT_NAME
,CONVERT(varchar(10),sh.SCHED_DATE,120) AS SCHED_DATE
FROM dbo.DateDimension dd
INNER JOIN Planning.ScheduleHistory sh
ON dd.Date = sh.EXTRACT_DATE
)
--CTE 2 VirtualQueues (ACTUALS) and Business Areas
, ctevq
AS
(
SELECT vq.StartDate
,ba.IEXDescription
,SUM(vq.Offered) AS Offered
FROM Planning.VirtualQueues vq
INNER JOIN
Planning.BusinessAreas ba
ON vq.VirtualQueue = ba.VirtualQueue
GROUP BY ba.IEXDescription, vq.StartDate
HAVING ba.IEXDescription = 'Personal'
OR
ba.IEXDescription = 'Corporate'
OR
ba.IEXDescription = 'Company'
)
-- Join the two CTEs
SELECT *
FROM ctevh
LEFT OUTER JOIN ctevq
ON ctevh.CT_NAME = ctevq.IEXDescription
AND
ctevh.SCHED_DATE = ctevq.StartDate
PIVOT (
MAX(ctevh.FORECAST_CALLS)
FOR PlanningWeekNo in ([1],[2],[3],[4])
) AS pvt
I get the following error which I think is being caused by the PIVOT part:
An error occurred while executing batch. Error message is: Index was outside the bounds of the array.
If I replace CTE2 'ctevq' with an actual (exact) VIEW then the script runs fine.
I've searched online and the overall solution seems to be to upgrade your SSMS/SQL Server. Neither of which is an option
