August 4, 2016 at 8:34 am
Query below -
I need to pull in the Person's extension - which is in the same same table as person_Id and person_name.. (mart.dim_person) is the table. In the dim_person table, the field I need to also pull in is [employment_number] which is their EXT
"I am getting this error "Conversion failed when converting the nvarchar value 'Brian Weese' to data type int."
What am I doing wrong? if I remove the person_Ext which and ,mart.dim_person.employment_number from the query, it works..
I can't figure this out..
CREATE TABLE #RESULT(date_id int, date smalldatetime,
person_id Int
,person_Ext Int (if I remove this it works)
,person_name nvarchar(200)
,scheduled_ready_time int
,deviation_s decimal(18,1)
,adherence decimal(18,1)
)
INSERT #RESULT
(date_id
,date
,person_id
,person_Ext
,person_name (if I remove this it works)
,scheduled_ready_time
,deviation_s
,adherence)
SELECT
mart.dim_date.date_id
,CONVERT (varchar,mart.dim_date.date_date,1) AS 'DATE'
,mart.dim_person.person_id
,mart.dim_person.person_name
---->,mart.dim_person.employment_number (If I remove this, it works)
,ISNULL(sum(mart.fact_schedule_deviation.scheduled_ready_time_s),0)
,ISNULL(SUM(mart.fact_schedule_deviation.deviation_schedule_ready_s),0) 'deviation_s'
,0
FROM
mart.fact_schedule_deviation
INNER JOIN mart.bridge_time_zone
ON mart.fact_schedule_deviation.interval_id= mart.bridge_time_zone.interval_id
AND mart.fact_schedule_deviation.date_id= mart.bridge_time_zone.date_id
INNER JOIN mart.dim_date
ON mart.bridge_time_zone.local_date_id = mart.dim_date.date_id
INNER JOIN mart.dim_interval
ON mart.bridge_time_zone.local_interval_id = mart.dim_interval.interval_id
INNER JOIN mart.dim_person
ON mart.dim_person.person_id=mart.fact_schedule_deviation.person_id
INNER JOIN mart.dim_time_zone
ON mart.dim_time_zone.time_zone_id=mart.bridge_time_zone.time_zone_id
--WHERE mart.dim_date.date_date between '2016-06-01' AND '2016-08-31'
where mart.dim_time_zone.time_zone_code ='Mountain Standard Time'
AND mart.dim_person.team_name IN ('Wolfe','Mabey','Curren','Meyer','Miller','CSMs')
GROUP BY
mart.dim_person.person_id
,mart.dim_person.person_name
,mart.dim_date.date_id
,mart.dim_date.date_date
,mart.dim_person.employment_number
DELETE FROM #RESULT WHERE scheduled_ready_time <= 0
UPDATE #RESULT SET adherence=100*(#RESULT.scheduled_ready_time - #RESULT.deviation_s )/ #RESULT.scheduled_ready_time
SELECT
person_name 'Agent'
,CONVERT (varchar,date,1) as DATE
,adherence 'Adherence (%)'
FROM #RESULT ORDER BY date ,person_name
DROP TABLE #RESULT
August 4, 2016 at 8:45 am
You're trying to insert from the dim_person.person_name column, which is (presumably) nvarchar into the person_Ext column in your temp table, which is int.
John
August 4, 2016 at 8:59 am
That was it too, but I need the person_ext to be an INT - but I figured it out, my select state was out of order.. I didn't realize this mattered. But I got it now, thanks!
CREATE TABLE #RESULT(date_id int, date smalldatetime,
person_id Int
,person_Ext Int
,person_name nvarchar(200)
,scheduled_ready_time int
,deviation_s decimal(18,1)
,adherence decimal(18,1)
)
INSERT #RESULT
(date_id
,date
,person_id
,person_Ext
,person_name
,scheduled_ready_time
,deviation_s
,adherence)
SELECT
mart.dim_date.date_id
,CONVERT (varchar,mart.dim_date.date_date,1) AS 'DATE'
,mart.dim_person.person_id
,mart.dim_person.employment_number
,mart.dim_person.person_name
,ISNULL(sum(mart.fact_schedule_deviation.scheduled_ready_time_s),0)
,ISNULL(SUM(mart.fact_schedule_deviation.deviation_schedule_ready_s),0) 'deviation_s'
,0
August 4, 2016 at 9:01 am
John Mitchell-245523 (8/4/2016)
You're trying to insert from the dim_person.person_name column, which is (presumably) nvarchar into the person_Ext column in your temp table, which is int.John
Yup,
Try flipping the order of those two columns in your insert, so you are putting the correct data in the correct column.
I've also see that error happen when using a comparison operator and forgetting the single quotes when comparing strings that are numerical.
August 4, 2016 at 9:28 am
andrew.leef (8/4/2016)
John Mitchell-245523 (8/4/2016)
You're trying to insert from the dim_person.person_name column, which is (presumably) nvarchar into the person_Ext column in your temp table, which is int.John
Yup,
Try flipping the order of those two columns in your insert, so you are putting the correct data in the correct column.
I've also see that error happen when using a comparison operator and forgetting the single quotes when comparing strings that are numerical.
Yup, These two fixed it, thanks again! You rock
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply