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

meetpavi gravatar image

meetpavi
40 4 4 5

(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

meetpavi gravatar image

meetpavi
40 4 4 5

"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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(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

meetpavi gravatar image

meetpavi
40 4 4 5

(comments are locked)
10|1200 characters needed characters left
yes it worked.. thanks!!!!
more ▼

answered Aug 27, 2012 at 01:43 PM

meetpavi gravatar image

meetpavi
40 4 4 5

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x729
x372
x81
x51

asked: Aug 24, 2012 at 01:18 PM

Seen: 1086 times

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