x

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
more ▼

asked Aug 24, 2012 at 01:18 PM in Default

avatar image

meetpavi
50 4 4 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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' "

more ▼

answered Aug 27, 2012 at 01:11 PM

avatar image

meetpavi
50 4 4 7

"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

Aug 27, 2012 at 01:41 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

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 functions to format the integer into a string.

more ▼

answered Aug 25, 2012 at 08:16 PM

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 27, 2012 at 12:12 PM

avatar image

meetpavi
50 4 4 7

(comments are locked)
10|1200 characters needed characters left

yes it worked.. thanks!!!!

more ▼

answered Aug 27, 2012 at 01:43 PM

avatar image

meetpavi
50 4 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x993
x426
x103
x56

asked: Aug 24, 2012 at 01:18 PM

Seen: 1396 times

Last Updated: Aug 27, 2012 at 01:43 PM

Copyright 2016 Redgate Software. Privacy Policy