Solved

Query errors with "Error converting varchar to numeric."

Posted on 2007-11-27
8
197 Views
Last Modified: 2010-04-21
I have a query (looking be formatted better in the very near future) but for now the query errors on the SWAP Rate dataset (located at the bottom of the code snippet in the last section). The first two sections generate fine but when the third is UNIONed  the error "Error converting varchar to numeric' is thrown even though all txtActivityNumber columns are varchar(20) formatted - - out of ideas to try and would love some expert assiatnce to lead me in the right direction.

Thanks Experts!!!
--(1) Scheduled Activities & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:

-------------------------------------------------------------------------------------

SELECT	DISTINCT sa.txtActivityNumber AS ActivityNumber, 

		'Scheduled_Activities' AS DataType, 

		(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company, 

		dbo.tblBranches.txtBranchName AS Branch, 

		HATUsers.intUserID AS HATID, 

		HATUsers.txtUserFirstName AS HATFirstName, 

		HATUsers.txtUserLastName AS HATLastName, 

		WTLUsers.intUserID AS WTLID, 

		WTLUsers.txtUserFirstName AS WTLFirstName, 

		WTLUsers.txtUserLastName AS WTLLastName, 

		dbo.tblUsers.intUserID AS TechID, 

		dbo.tblUsers.txtUserFirstName AS TechFirstName, 

		dbo.tblUsers.txtUserLastName AS TechLastName, 

		CONVERT(CHAR(10), sa.dteActivityDueRT, 101) AS Date, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 'COV' ELSE 'POV' END) AS COV_POV, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 1 ELSE 0 END) AS COVOccuranceCount, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NULL THEN 1 ELSE 0 END) AS POVOccuranceCount, 

		(CASE WHEN dbo.tblTicketDTV.bitTechOnSite = 0 THEN 1 ELSE 0 END) AS TNOS_Count, 

		1 AS ScheduledActivitiesCount, 

		'' AS CompletedActivitiesCount, 

		'' AS ProductivityPoints, 

		dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,

		(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 'Yes' ELSE 'No' END) AS RepeatServiceFlag,

		(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 1 ELSE 0 END) AS RepeatServiceOccuranceCount,

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 5 THEN 1 ELSE 0 END) AS Oustanding, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 4 THEN 1 ELSE 0 END) AS VerySatisfied, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 3 THEN 1 ELSE 0 END) AS Acceptable, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 2 THEN 1 ELSE 0 END) AS NotSoGood, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 1 THEN 1 ELSE 0 END) AS Poor,

		'' AS SWAPRateCount
 

FROM	dbo.tblSiebelMgnt 

		RIGHT OUTER JOIN dbo.tblTicketDTV 

		RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelActivity1000

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1200

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1400

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1800) AS sa ON dbo.tblTicketDTV.txtJobNumber = sa.txtActivityNumber 

		LEFT OUTER JOIN dbo.tblUsers AS HATUsers 

		INNER JOIN dbo.tblUsers AS WTLUsers 

		INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber 

		LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID 

		LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON CAST(SUBSTRING(sa.txtTechUserID, 6, LEN(sa.txtTechUserID)) AS BIGINT) = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sa.txtServiceRegion 

		LEFT OUTER JOIN dbo.tblActivitySurvey ON sa.intSiebelActivityID = dbo.tblActivitySurvey.intSiebelActivityID
 

WHERE	((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

 			   WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND 

		(dbo.tblBranches.txtBranchName IS NOT NULL) AND 

		(dbo.tblUsers.intUserID IS NOT NULL) AND 

		(sa.dteActivityDueRT BETWEEN '11/15/2007' AND '11/16/2007')
 

-----------------------------------------------------------------------------------------------------------------------------------------------------

UNION

-----------------------------------------------------------------------------------------------------------------------------------------------------
 

--(2) Completed Activities & (4) Productivity Points & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:

---------------------------------------------------------------------------------------------------------------

SELECT	DISTINCT sc.txtActivityNumber AS ActivityNumber,

		'Completed_Activities' AS DataType,

		(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company, 

		dbo.tblBranches.txtBranchName AS Branch,

		HATUsers.intUserID AS HATID, 

		HATUsers.txtUserFirstName AS HATFirstName, 

		HATUsers.txtUserLastName AS HATLastName,

		WTLUsers.intUserID AS WTLID, 

		WTLUsers.txtUserFirstName AS WTLFirstName, 

		WTLUsers.txtUserLastName AS WTLLastName, 

		dbo.tblUsers.intUserID AS TechID, 

		dbo.tblUsers.txtUserFirstName AS TechFirstName, 

		dbo.tblUsers.txtUserLastName AS TechLastName, 

		CONVERT(CHAR(10), sc.dteActualEndDate, 101) AS Date,

		'' AS COV_POV,

		'' AS COVOccuranceCount,

		'' AS POVOccuranceCount,

		'' AS TNOS_Count,

		'' AS ScheduledActivitiesCount,

		1 AS CompletedActivitiesCount,

		sc.intProductivityPoints AS ProductivityPoints,

		dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,

		'' AS RepeatServiceFlag,

		'' AS RepeatServiceOccuranceCount,

		'' AS Oustanding, 

		'' AS VerySatisfied, 

		'' AS Acceptable, 

		'' AS NotSoGood, 

		'' AS Poor,

		'' AS SWAPRateCount

		

FROM	dbo.tblSiebelMgnt 

		RIGHT OUTER JOIN dbo.tblTicketDTV 

		RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelComplete1000

						  UNION

						  SELECT * FROM dbo.tblSiebelComplete1200

						  UNION

						  SELECT * FROM dbo.tblSiebelComplete1400

						  UNION

						  SELECT  * FROM dbo.tblSiebelComplete1800) AS sc ON dbo.tblTicketDTV.txtJobNumber = sc.txtActivityNumber 

		LEFT OUTER JOIN dbo.tblUsers AS HATUsers 

		INNER JOIN dbo.tblUsers AS WTLUsers 

		INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber 

		LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID 

		LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON sc.intTechID = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sc.txtServiceRegion
 

WHERE	((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND 

		(dbo.tblBranches.txtBranchName IS NOT NULL) AND

		(dbo.tblUsers.intUserID IS NOT NULL) AND

		(sc.dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
 

-----------------------------------------------------------------------------------------------------------------------------------------------------

UNION

-----------------------------------------------------------------------------------------------------------------------------------------------------
 

--(10 & 11) SWAP Rate:

----------------------

SELECT	DISTINCT scd.txtActivityNumber AS ActivityNumber,

		'SWAP_Rate' AS DataType,

		'' AS Company, 

		'' AS Branch,

		'' AS HATID, 

		'' AS HATFirstName, 

		'' AS HATLastName,

		'' AS WTLID, 

		'' AS WTLFirstName, 

		'' AS WTLLastName, 

		'' AS TechID, 

		'' AS TechFirstName, 

		'' AS TechLastName, 

		'' AS Date,

		'' AS COV_POV,

		'' AS COVOccuranceCount,

		'' AS POVOccuranceCount,

		'' AS TNOS_Count,

		'' AS ScheduledActivitiesCount,

		'' AS CompletedActivitiesCount,

		'' AS ProductivityPoints,

		'' AS TechEfficiencyRate,

		'' AS RepeatServiceFlag,

		'' AS RepeatServiceOccuranceCount,

		'' AS Oustanding, 

		'' AS VerySatisfied, 

		'' AS Acceptable, 

		'' AS NotSoGood, 

		'' AS Poor,

		1 AS SWAPRateCount
 

FROM	(SELECT * FROM dbo.tblSiebelCompleteDetail1000

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1200

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1400

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1800) AS scd
 

WHERE	--(scd.txtActionRequired = 'SWAP Relocate') AND

		(scd.txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')) AND

		(scd.txtActivityNumber IN ((SELECT txtActivityNumber FROM dbo.tblSiebelComplete1000 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1200 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1400 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1800 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'))))

Open in new window

0
Comment
Question by:Levi Martin
  • 3
  • 3
  • 2
8 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 250 total points
ID: 20360163
Two things.

First, you are far too complex.  The query for this last section need only be:

SELECT  DISTINCT
      scd.txtActivityNumber AS ActivityNumber,
      'SWAP_Rate' AS DataType,
      '' AS Company,
      '' AS Branch,
      '' AS HATID,
      '' AS HATFirstName,
      '' AS HATLastName,
      '' AS WTLID,
      '' AS WTLFirstName,
      '' AS WTLLastName,
      '' AS TechID,
      '' AS TechFirstName,
      '' AS TechLastName,
      '' AS Date,
      '' AS COV_POV,
      '' AS COVOccuranceCount,
      '' AS POVOccuranceCount,
      '' AS TNOS_Count,
      '' AS ScheduledActivitiesCount,
      '' AS CompletedActivitiesCount,
      '' AS ProductivityPoints,
      '' AS TechEfficiencyRate,
      '' AS RepeatServiceFlag,
      '' AS RepeatServiceOccuranceCount,
      '' AS Oustanding,
      '' AS VerySatisfied,
      '' AS Acceptable,
      '' AS NotSoGood,
      '' AS Poor,
      1 AS SWAPRateCount
 
FROM (
      SELECT txtActivityNumber
      FROM dbo.tblSiebelCompleteDetail1000
      WHERE txtOrderSubType = 'Service'
            AND dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'
            AND txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')
      UNION
      SELECT txtActivityNumber
      FROM dbo.tblSiebelCompleteDetail1200
      WHERE txtOrderSubType = 'Service'
            AND dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'
            AND txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')
      UNION
      SELECT txtActivityNumber
      FROM dbo.tblSiebelCompleteDetail1400
      WHERE txtOrderSubType = 'Service'
            AND dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'
            AND txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')
      UNION
      SELECT txtActivityNumber
      FROM dbo.tblSiebelCompleteDetail1800
      WHERE txtOrderSubType = 'Service'
            AND dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'
            AND txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')
      ) AS scd
 

Second, the problem is not in the WHERE, but in the data selected.  For example, you have....

      '' AS HATID,

Looking at the rest of the query, HATID is numeric, not text.  Try:

      0 AS HATID,

or

      NULL AS HATID,
0
 

Author Comment

by:Levi Martin
ID: 20360497
Thanks for the quick response bhess1,

Tried your suggestions and found the following:
(1) Could not restructure the code for SWAP Rate as I have Complete and CompleteDetail tables although putting everything into the FROM statement was a great idea that I haven't attempted before.

(2) Could not get the 0 or NULL to work for ''ID (HAT, WTL, Tech) as I still receive a error on txtActivityNumber for could not convert varchar to numeric - - I was originally uising the following code snippet for the SWAP Rate and receive the same error still.

Thanks for you help and continuing suggestion greatly!!!
--(10 & 11) SWAP Rate:

----------------------

SELECT	DISTINCT scd.txtActivityNumber AS ActivityNumber, 

		'SWAP_Rate' AS DataType, 

		(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company, 

		dbo.tblBranches.txtBranchName AS Branch, 

		HATUsers.intUserID AS HATID, 

		HATUsers.txtUserFirstName AS HATFirstName, 

		HATUsers.txtUserLastName AS HATLastName, 

		WTLUsers.intUserID AS WTLID, 

		WTLUsers.txtUserFirstName AS WTLFirstName, 

		WTLUsers.txtUserLastName AS WTLLastName, 

		dbo.tblUsers.intUserID AS TechID, 

		dbo.tblUsers.txtUserFirstName AS TechFirstName, 

		dbo.tblUsers.txtUserLastName AS TechLastName, 

		CONVERT(CHAR(10), sc.dteActualEndDate, 101) AS Date, 

		'' AS COV_POV, 

		'' AS COVOccuranceCount, 

		'' AS POVOccuranceCount, 

		'' AS TNOS_Count, 

		'' AS ScheduledActivitiesCount, 

		'' AS CompletedActivitiesCount, 

		'' AS ProductivityPoints, 

		'' AS TechEfficiencyRate, 

		'' AS RepeatServiceFlag, 

		'' AS RepeatServiceOccuranceCount, 

		'' AS Oustanding, 

		'' AS VerySatisfied, 

		'' AS Acceptable, 

		'' AS NotSoGood, 

		'' AS Poor, 

		1 AS SWAPRateCount
 

FROM	dbo.tblUsers AS WTLUsers 

		INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber 

		INNER JOIN dbo.tblUsers AS HATUsers ON dbo.tblUsers.intMgrNumber = HATUsers.intUserID 

		RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelCompleteDetail1000

						  UNION

						  SELECT * FROM  dbo.tblSiebelCompleteDetail1200

						  UNION

						  SELECT * FROM dbo.tblSiebelCompleteDetail1400

						  UNION

						  SELECT * FROM dbo.tblSiebelCompleteDetail1800) AS scd ON dbo.tblUsers.intUserID = scd.intTechID 

		INNER JOIN (SELECT * FROM dbo.tblSiebelComplete1000

					UNION

					SELECT * FROM dbo.tblSiebelComplete1200

					UNION

					SELECT * FROM dbo.tblSiebelComplete1400

					UNION

					SELECT * FROM dbo.tblSiebelComplete1800) AS sc ON scd.txtActivityNumber = sc.txtActivityNumber 

		LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID
 

WHERE	((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND 

		(dbo.tblBranches.txtBranchName IS NOT NULL) AND 

		(dbo.tblUsers.intUserID IS NOT NULL) AND 

		(scd.txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')) AND 

		(scd.txtActivityNumber IN ((SELECT	txtActivityNumber

									FROM	dbo.tblSiebelComplete1000 AS tblSiebelComplete1000_1

									WHERE	(txtOrderSubType = 'Service') AND

											(dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

									UNION

									SELECT	txtActivityNumber

									FROM	dbo.tblSiebelComplete1200 AS tblSiebelComplete1200_1

									WHERE	(txtOrderSubType = 'Service') AND

											(dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

									UNION

									SELECT	txtActivityNumber

									FROM	dbo.tblSiebelComplete1400 AS tblSiebelComplete1400_1

									WHERE	(txtOrderSubType = 'Service') AND

											(dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

									UNION

									SELECT	txtActivityNumber

									FROM	dbo.tblSiebelComplete1800 AS tblSiebelComplete1800_1

									WHERE	(txtOrderSubType = 'Service') AND

											(dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')))) AND

		(sc.dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

Open in new window

0
 
LVL 32

Expert Comment

by:bhess1
ID: 20360582
Checking:

If you run from line 1 to line 123 as one process, this works, correct?

If you run line 124 down as one process, does this work?
0
 

Author Comment

by:Levi Martin
ID: 20360720
Yes, 1-123 works and 124-End works - - I receive the error when the all is combined to run on the txtActivityNumber field.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 25

Expert Comment

by:imitchie
ID: 20361092
point1:
UNION is explicitly DISTINCT across all UNIONed sets. so there is no need for DISTINCT within the top/bottom SQL
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20361151
can you please do this:
--(1) Scheduled Activities & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:

-------------------------------------------------------------------------------------

SELECT top 0	sa.txtActivityNumber AS ActivityNumber, 

		'Scheduled_Activities' AS DataType, 

		(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company, 

		dbo.tblBranches.txtBranchName AS Branch, 

		HATUsers.intUserID AS HATID, 

		HATUsers.txtUserFirstName AS HATFirstName, 

		HATUsers.txtUserLastName AS HATLastName, 

		WTLUsers.intUserID AS WTLID, 

		WTLUsers.txtUserFirstName AS WTLFirstName, 

		WTLUsers.txtUserLastName AS WTLLastName, 

		dbo.tblUsers.intUserID AS TechID, 

		dbo.tblUsers.txtUserFirstName AS TechFirstName, 

		dbo.tblUsers.txtUserLastName AS TechLastName, 

		CONVERT(CHAR(10), sa.dteActivityDueRT, 101) AS Date, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 'COV' ELSE 'POV' END) AS COV_POV, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 1 ELSE 0 END) AS COVOccuranceCount, 

		(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NULL THEN 1 ELSE 0 END) AS POVOccuranceCount, 

		(CASE WHEN dbo.tblTicketDTV.bitTechOnSite = 0 THEN 1 ELSE 0 END) AS TNOS_Count, 

		1 AS ScheduledActivitiesCount, 

		'' AS CompletedActivitiesCount, 

		'' AS ProductivityPoints, 

		dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,

		(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 'Yes' ELSE 'No' END) AS RepeatServiceFlag,

		(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 1 ELSE 0 END) AS RepeatServiceOccuranceCount,

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 5 THEN 1 ELSE 0 END) AS Oustanding, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 4 THEN 1 ELSE 0 END) AS VerySatisfied, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 3 THEN 1 ELSE 0 END) AS Acceptable, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 2 THEN 1 ELSE 0 END) AS NotSoGood, 

		(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 1 THEN 1 ELSE 0 END) AS Poor,

		'' AS SWAPRateCount

into tmp1__

FROM	dbo.tblSiebelMgnt 

		RIGHT OUTER JOIN dbo.tblTicketDTV 

		RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelActivity1000

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1200

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1400

						  UNION

						  SELECT * FROM dbo.tblSiebelActivity1800) AS sa ON dbo.tblTicketDTV.txtJobNumber = sa.txtActivityNumber 

		LEFT OUTER JOIN dbo.tblUsers AS HATUsers 

		INNER JOIN dbo.tblUsers AS WTLUsers 

		INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber 

		LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID 

		LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON CAST(SUBSTRING(sa.txtTechUserID, 6, LEN(sa.txtTechUserID)) AS BIGINT) = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sa.txtServiceRegion 

		LEFT OUTER JOIN dbo.tblActivitySurvey ON sa.intSiebelActivityID = dbo.tblActivitySurvey.intSiebelActivityID

 

WHERE	((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

 			   WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			   WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND 

		(dbo.tblBranches.txtBranchName IS NOT NULL) AND 

		(dbo.tblUsers.intUserID IS NOT NULL) AND 

		(sa.dteActivityDueRT BETWEEN '11/15/2007' AND '11/16/2007')

 
 

-----------------------------------------------------------------------------------------------------------------------------------------------------
 

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

--(2) Completed Activities & (4) Productivity Points & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:

---------------------------------------------------------------------------------------------------------------

SELECT	top 0 sc.txtActivityNumber AS ActivityNumber,

		'Completed_Activities' AS DataType,

		(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company, 

		dbo.tblBranches.txtBranchName AS Branch,

		HATUsers.intUserID AS HATID, 

		HATUsers.txtUserFirstName AS HATFirstName, 

		HATUsers.txtUserLastName AS HATLastName,

		WTLUsers.intUserID AS WTLID, 

		WTLUsers.txtUserFirstName AS WTLFirstName, 

		WTLUsers.txtUserLastName AS WTLLastName, 

		dbo.tblUsers.intUserID AS TechID, 

		dbo.tblUsers.txtUserFirstName AS TechFirstName, 

		dbo.tblUsers.txtUserLastName AS TechLastName, 

		CONVERT(CHAR(10), sc.dteActualEndDate, 101) AS Date,

		'' AS COV_POV,

		'' AS COVOccuranceCount,

		'' AS POVOccuranceCount,

		'' AS TNOS_Count,

		'' AS ScheduledActivitiesCount,

		1 AS CompletedActivitiesCount,

		sc.intProductivityPoints AS ProductivityPoints,

		dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,

		'' AS RepeatServiceFlag,

		'' AS RepeatServiceOccuranceCount,

		'' AS Oustanding, 

		'' AS VerySatisfied, 

		'' AS Acceptable, 

		'' AS NotSoGood, 

		'' AS Poor,

		'' AS SWAPRateCount

into tmp2__

FROM	dbo.tblSiebelMgnt 

		RIGHT OUTER JOIN dbo.tblTicketDTV 

		RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelComplete1000

						  UNION

						  SELECT * FROM dbo.tblSiebelComplete1200

						  UNION

						  SELECT * FROM dbo.tblSiebelComplete1400

						  UNION

						  SELECT  * FROM dbo.tblSiebelComplete1800) AS sc ON dbo.tblTicketDTV.txtJobNumber = sc.txtActivityNumber 

		LEFT OUTER JOIN dbo.tblUsers AS HATUsers 

		INNER JOIN dbo.tblUsers AS WTLUsers 

		INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber 

		LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID 

		LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON sc.intTechID = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sc.txtServiceRegion

 

WHERE	((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast' 

			  WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND 

		(dbo.tblBranches.txtBranchName IS NOT NULL) AND

		(dbo.tblUsers.intUserID IS NOT NULL) AND

		(sc.dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

 

-----------------------------------------------------------------------------------------------------------------------------------------------------

UNION

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

--(10 & 11) SWAP Rate:

----------------------

SELECT	DISTINCT scd.txtActivityNumber AS ActivityNumber,

		'SWAP_Rate' AS DataType,

		'' AS Company, 

		'' AS Branch,

		'' AS HATID, 

		'' AS HATFirstName, 

		'' AS HATLastName,

		'' AS WTLID, 

		'' AS WTLFirstName, 

		'' AS WTLLastName, 

		'' AS TechID, 

		'' AS TechFirstName, 

		'' AS TechLastName, 

		'' AS Date,

		'' AS COV_POV,

		'' AS COVOccuranceCount,

		'' AS POVOccuranceCount,

		'' AS TNOS_Count,

		'' AS ScheduledActivitiesCount,

		'' AS CompletedActivitiesCount,

		'' AS ProductivityPoints,

		'' AS TechEfficiencyRate,

		'' AS RepeatServiceFlag,

		'' AS RepeatServiceOccuranceCount,

		'' AS Oustanding, 

		'' AS VerySatisfied, 

		'' AS Acceptable, 

		'' AS NotSoGood, 

		'' AS Poor,

		1 AS SWAPRateCount

 

FROM	(SELECT * FROM dbo.tblSiebelCompleteDetail1000

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1200

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1400

		 UNION

		 SELECT * FROM dbo.tblSiebelCompleteDetail1800) AS scd

 

WHERE	--(scd.txtActionRequired = 'SWAP Relocate') AND

		(scd.txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')) AND

		(scd.txtActivityNumber IN ((SELECT txtActivityNumber FROM dbo.tblSiebelComplete1000 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1200 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1400 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')

								    UNION

								    SELECT txtActivityNumber FROM dbo.tblSiebelComplete1800 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'))))
 
 

select a.name, b.name, xtype, length, xprec, xscale

from syscolumns a, syscolumns b

where a.id = object_id('tmp1')

  and b.id = object_id('tmp2')

Open in new window

0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 250 total points
ID: 20361179
that will reveal where you have type mismatches between top query and bottom query. sorry that last syscolumns query should be (last line missing)

remember to drop the tables later

drop table tmp1__
drop table tmp2__

I can tell you that you will find many differences, ie.
tmp2__ '' AS Poor = char/varchar
tmp1__ (CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 1 THEN 1 ELSE 0 END) AS Poor = int (1 or 0)


select a.name, b.name, xtype, length, xprec, xscale

from syscolumns a, syscolumns b

where a.id = object_id('tmp1')

  and b.id = object_id('tmp2')

  and a.colid = b.colid

Open in new window

0
 

Author Closing Comment

by:Levi Martin
ID: 31411261
Thanks so much to the experts who helped me understand this now simple and basic concept -- very much appreciated I hope that I too can pass on the knowledge. Many thanks experts!!!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now