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
