question

The below code is written to perform a pivot function, But is giving me the following error... Can some one let me know where i have gone wrong? Msg 245, Level 16, State 1, Line 117 Conversion failed when converting the varchar value '],[' to data type int. EXEC sp_dbcmptlevel 'VMFG', 100 GO declare @startdate as datetime declare @enddate as datetime declare @resourceid as varchar(20) declare @query as varchar(2000) declare @weekno as nvarchar(2000) set @startdate = '2012-07-01' set @enddate = '2012-08-01' set @resourceid = 'plant 6' SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, OPERATION_SEQ_NO, EMPLOYEE_ID, TRANSACTION_DATE, HOURS_WORKED, TYPE, RESOURCE_ID, GOOD_QTY INTO #TEMP_0 FROM LABOR_TICKET WHERE CAST(CONVERT(varchar, TRANSACTION_DATE, 110) AS DateTime) >= @startdate AND CAST(CONVERT(varchar, TRANSACTION_DATE, 110) AS DateTime) < DATEADD(D, 1, @enddate) AND WORKORDER_TYPE = 'W' SELECT #TEMP_0.WORKORDER_BASE_ID, #TEMP_0.WORKORDER_LOT_ID, #TEMP_0.WORKORDER_SPLIT_ID, #TEMP_0.WORKORDER_SUB_ID, #TEMP_0.OPERATION_SEQ_NO, #TEMP_0.EMPLOYEE_ID, #TEMP_0.TRANSACTION_DATE, MIN(LABOR_TICKET.TRANSACTION_DATE) AS MIN_DATE, #TEMP_0.HOURS_WORKED, #TEMP_0.TYPE, #TEMP_0.GOOD_QTY, #TEMP_0.RESOURCE_ID INTO #TEMP_1 FROM #TEMP_0 INNER JOIN LABOR_TICKET ON #TEMP_0.WORKORDER_BASE_ID = LABOR_TICKET.WORKORDER_BASE_ID AND #TEMP_0.WORKORDER_LOT_ID = LABOR_TICKET.WORKORDER_LOT_ID AND #TEMP_0.WORKORDER_SPLIT_ID = LABOR_TICKET.WORKORDER_SPLIT_ID AND #TEMP_0.WORKORDER_SUB_ID = LABOR_TICKET.WORKORDER_SUB_ID AND #TEMP_0.OPERATION_SEQ_NO = LABOR_TICKET.OPERATION_SEQ_NO GROUP BY #TEMP_0.WORKORDER_BASE_ID, #TEMP_0.WORKORDER_LOT_ID, #TEMP_0.WORKORDER_SPLIT_ID, #TEMP_0.WORKORDER_SUB_ID, #TEMP_0.OPERATION_SEQ_NO, #TEMP_0.RESOURCE_ID, #TEMP_0.EMPLOYEE_ID, #TEMP_0.TRANSACTION_DATE, #TEMP_0.HOURS_WORKED, #TEMP_0.TYPE, #TEMP_0.GOOD_QTY, LABOR_TICKET.WORKORDER_TYPE, LABOR_TICKET.TYPE HAVING LABOR_TICKET.WORKORDER_TYPE = 'W' SELECT #TEMP_1.*, SEQUENCE_NO, SETUP_HRS, RUN, CALC_START_QTY, HRS = CASE WHEN RUN_TYPE = 'MIN/PC' THEN (RUN / 60) * GOOD_QTY WHEN RUN_TYPE = 'HRS/PC' THEN RUN * GOOD_QTY WHEN RUN_TYPE = 'PCS/HR' THEN CASE RUN WHEN '0' THEN GOOD_QTY / (RUN + 1) ELSE GOOD_QTY / RUN END WHEN RUN_TYPE = 'PCS/MIN' THEN CASE RUN WHEN '0' THEN (GOOD_QTY / (RUN+1) )/ 60 ELSE (GOOD_QTY / RUN ) / 60 END WHEN RUN_TYPE = 'HRS/LOAD' AND GOOD_QTY > 0 AND ((GOOD_QTY / LOAD_SIZE_QTY ) ) * RUN 0 AND ((GOOD_QTY / LOAD_SIZE_QTY ) ) * RUN >= RUN THEN ((GOOD_QTY / LOAD_SIZE_QTY ) + 1 ) * RUN WHEN RUN_TYPE = 'DAYS/LOAD' AND GOOD_QTY > 0 AND (((GOOD_QTY / LOAD_SIZE_QTY ) ) * RUN) / 24 0 AND (((GOOD_QTY / LOAD_SIZE_QTY ) ) * RUN) / 24 >= RUN THEN (((GOOD_QTY / LOAD_SIZE_QTY ) + 1 ) / 24 ) * RUN WHEN RUN_TYPE = 'DAYS/PC' THEN RUN * GOOD_QTY / 24 ELSE 0 END, SETUP_COST_PER_HR, RUN_COST_PER_HR INTO #TEMP_2 FROM #TEMP_1 INNER JOIN OPERATION ON #TEMP_1.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID AND #TEMP_1.WORKORDER_LOT_ID = OPERATION.WORKORDER_LOT_ID AND #TEMP_1.WORKORDER_SPLIT_ID = OPERATION.WORKORDER_SPLIT_ID AND #TEMP_1.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID AND #TEMP_1.OPERATION_SEQ_NO = OPERATION.SEQUENCE_NO WHERE OPERATION.WORKORDER_TYPE = 'W' SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, EMPLOYEE_ID, GOOD_QTY, TRANSACTION_DATE, MIN_DATE, HOURS_WORKED, SETUP_HRS, CALC_START_QTY, SETUP = CASE WHEN TRANSACTION_DATE = MIN_DATE THEN SETUP_HRS ELSE 0 END, SETUP_COST_PER_HR, HRS, RUN_COST_PER_HR, RESOURCE_ID INTO #TEMP_3 FROM #TEMP_2 SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, EMPLOYEE_ID, SETUP, HRS, GOOD_QTY, TRANSACTION_DATE, RESOURCE_ID, SETUP*SETUP_COST_PER_HR AS SETUP_COST, CALC_START_QTY, HRS*RUN_COST_PER_HR AS RUN_COST INTO #SPTEMPFINAL FROM #TEMP_3 SELECT WORKORDER_BASE_ID, WORKORDER_SPLIT_ID,WORKORDER_LOT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, EMPLOYEE_ID, GOOD_QTY, CALC_START_QTY, TRANSACTION_DATE, SETUP_COST, RUN_COST, SETUP_COST+RUN_COST AS TOT_COST, SETUP+HRS AS TOT_TIME, RESOURCE_ID,DATEPART(WK, TRANSACTION_DATE) AS WEEK_NO, FIRST_DAY = CASE WHEN DATEPART(DW, TRANSACTION_DATE) = 1 THEN DATEADD(D, 1, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 2 THEN DATEADD(D, 0, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 3 THEN DATEADD(D, -1, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 4 THEN DATEADD(D, -2, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 5 THEN DATEADD(D, -3, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 6 THEN DATEADD(D, -4, TRANSACTION_DATE) WHEN DATEPART(DW, TRANSACTION_DATE) = 7 THEN DATEADD(D, -5, TRANSACTION_DATE) END INTO #TEMP_5 FROM #SPTEMPFINAL select DISTINCT WORKORDER_BASE_ID INTO #TEMP_6 from #temp_5 WHERE RESOURCE_ID = @resourceid SELECT #TEMP_5.* into #TEMP_7 FROM #TEMP_6 LEFT JOIN #TEMP_5 ON #TEMP_6.WORKORDER_BASE_ID = #TEMP_5.WORKORDER_BASE_ID ORDER BY #TEMP_5.WORKORDER_BASE_ID select RESOURCE_ID AS GROUPING,WEEK_NO,TOT_TIME from #TEMP_7 GROUP BY RESOURCE_ID,WEEK_NO,TOT_TIME SELECT @weekno = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + (WEEK_NO) FROM #TEMP_7 ORDER BY '],[' + (WEEK_NO) FOR XML PATH('')),1,2,'')+ ']' SET @query = 'SELECT * FROM ( SELECT RESOURCE_ID,WEEK_NO,TOT_TIME FROM #TEMP_7)t PIVOT (SUM(TOT_TIME) FOR WEEK_NO IN ('+@weekno+')) as pvt' exec @query DROP TABLE #TEMP_0 DROP TABLE #TEMP_1 DROP TABLE #TEMP_2 DROP TABLE #TEMP_3 DROP TABLE #TEMP_5 DROP TABLE #TEMP_6 DROP TABLE #TEMP_7 DROP TABLE #SPTEMPFINAL