question

meetpavi avatar image
meetpavi asked

Dynamic Pivot SQL 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
sqlquerypivotdynamic
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

meetpavi avatar image
meetpavi answered
Ok,i modified the script to include string and now its giving me an error saying "Msg 208, Level 16, State o, Line 1 invalid object name '##temp_8' I do not have a temp table '##temp_8' " SELECT @weekno = STUFF(( SELECT DISTINCT '],[' + LTRIM(STR((WEEK_NO))) FROM #TEMP_8 ORDER BY '],[' + LTRIM(STR(WEEK_NO)) FOR XML PATH('') ), 1, 2, '') + ']' SET @query = 'SELECT * FROM ( SELECT RESOURCE_ID,WEEK_NO,TOT_TIME FROM ##TEMP_8 )t PIVOT (SUM(TOT_TIME) FOR WEEK_NO IN ( '+@weekno+')) as pvt' "
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
"I do not have a temp table ##temp_8", you say... replace `##temp_8` with `#temp_8` in the second statement you've just copied and pasted into this answer
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
I suspect the problem lies with this bit of code: SELECT @weekno = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + (WEEK_NO) FROM #TEMP_7 ORDER BY '],[' + (WEEK_NO) FOR XML PATH('')),1,2,'')+ ']' What I think is happening is that your database structure has `WEEK_NO` declared as an `INT`. When you try to build a string like that, you may have to use one of the [CAST or CONVERT][1] functions to format the integer into a string. [1]: http://msdn.microsoft.com/en-us/library/ms187928.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

meetpavi avatar image
meetpavi answered
It gives me an error when i try to add str(weekno) or even a cast or convert.. Can some one please help me with the error in my code, i would really appreciate it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

meetpavi avatar image
meetpavi answered
yes it worked.. thanks!!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.