SQL Server 2005 Question

SQL 2005 - Conversion failed when converting the varchar value '6.2' to data type int.

Please note, the SQL is handled dynamically by the server, therefore some items in my WHERE clause will look odd to you - please disregard this. I am getting the following error and need a hand.

Msg 245, Level 16, State 1, Line 113
Conversion failed when converting the varchar value '6.2' to data type int.

SET NOCOUNT ON
 
CREATE TABLE #Patients (
	PatientProfileID int,
	PatientID varchar(15),
	MRN varchar (15),
	PID numeric,
	PatientName varchar(100),
	Birthdate datetime,
	Age varchar (15),
	Sex varchar(1),
	RaceCode varchar(50),
	EthnicityCode varchar(50),
	RaceMID2 int
)
 
DECLARE @AgeDate datetime
DECLARE @DXDate datetime
 
SET @AgeDate = '12/31/' + CONVERT(varchar, YEAR('08/31/2009'))
SET @DXDate = '06/30/' + CONVERT(varchar, YEAR('08/31/2009'))
 
INSERT INTO #Patients
SELECT pp.PatientProfileID, 
	pp.PatientID,
	pp.MedicalRecordNumber AS MRN,
	pp.PID,
	RTRIM(RTRIM(ISNULL(pp.Last, '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) AS PatientName,  
	pp.Birthdate,
	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) as Age, 
	pp.Sex,
	CASE WHEN r.Code = 'H' OR e.Code = 'H' THEN 'Hispanic/Latino' 
		WHEN r.Code = 'A' THEN 'Asian'
		WHEN r.Code = 'NH' THEN 'Native Hawaiian'
		WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
		WHEN r.Code = 'B' THEN 'Black/African American'
		WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
		WHEN r.Code = 'W' THEN 'White'
		WHEN r.Code = 'M' THEN 'More than one race'
		WHEN r.Code = 'U' THEN 'Unreported'
		ELSE 'Unreported' END AS RaceCode,
	CASE WHEN r.Code = 'H'  OR e.Code = 'H' THEN 'Hispanic/Latino' 
		ELSE 'All Others' END AS EthnicityCode, 
	cri.RaceMID2
 
 
FROM PatientProfile pp
	LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
	LEFT JOIN cusCRIInterview cri on pp.patientprofileid = cri.patientprofileid
	LEFT JOIN Medlists r on pp.RaceMID = r.MedListsID
	LEFT JOIN cusCRIMedLists e on cri.RaceMID2 = e.MedListsID
	
WHERE 
       --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
     	 AND 
	--Filter on Company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND
	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) >17.99
	AND 
	DATEPART(Year,pv.visit) = DATEPART(Year,'08/31/2009') -- limit to visits in this year
	AND
	pp.PatientProfileID in -- now we filter on diagnosis, independently
	(
		SELECT PatientProfileID
		FROM PatientProfile pp
		JOIN Orders o ON pp.PID = o.PID
		JOIN Orddx  ON o.dxgroupid = orddx.dxgroupid
		WHERE
		orddx.dxcode  LIKE ('icd-401.%') 
		AND  o.XID=1000000000000000000
		AND o.OrderDate <= @DXDate
	)
GROUP BY pp.PatientProfileID,pp.PatientId, pp.MedicalRecordNumber, pp.PID, pp.Birthdate, pp.Sex, pp.Last, pp.Suffix, pp.First, pp.Middle, r.code, e.code, cri.raceMID2
HAVING COUNT(*)>1 -- must have 2+ visits this year
 
 
SELECT PatientProfileID,
	count(*) as cnt,
	PatientID,
	MRN,
	PID,
	PatientName,  
	Birthdate,
	Age, 
	Sex,
	RaceCode,
	EthnicityCode, 
	RaceMID2
INTO #tmp
FROM #Patients 
GROUP BY PatientProfileID, PatientID, MRN, PID, PatientName, Birthdate, Sex, age, RaceCode, EthnicityCode, RaceMID2
ORDER BY	CASE
	WHEN '1'  = 1 THEN MRN
	WHEN '1'  = 2 THEN PatientName 
	WHEN '1'  = 3 THEN PatientID ELSE NULL
	END
 
DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
 
SET @StartDate  = CONVERT(varchar, YEAR('08/31/2009')) + '/01/01'
SET @EndDate = CONVERT(varchar, YEAR('08/31/2009')) + '/12/31'
 
ALTER TABLE #TMP ADD ObsValue varchar (2000) null
Update #TMP
Set ObsValue = '1'
FROM OBS obs
JOIN #TMP ON obs.PID = #TMP.pid
join (select pid, max(obsdate) as obsmax from obs WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90)) 
and obsdate >= @StartDate AND obsdate <= @EndDate group by pid) t on obs.PID=t.PID and obs.obsdate=t.obsmax
WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90))
 
 
Select * FROM #TMP 
Drop Table #tmp
Drop Table #Patients

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:

Select allOpen in new window

Trusted by Thousands of Top Companies

201410-LO-Qu-022

Related Questions We have nearly 4 million solutions here.

See More SQL Server 2005 Solutions

Solve Your Difficult Tech Problems Faster with Experts Exchange

Access millions of verified solutions and get 1-on-1 help from the experts.

Try It Free

30 day free trial.

Experts Exchange gives me a day to day reference of proven solutions that provide me guidance and troubleshooting help for my own clients.

- Brian B. Forte Consulting

Experts Exchange powers the growth and success
of technology professionals worldwide.

  • Solve

    Experts Exchange is the tech professional’s trusted, on-demand resource for solving difficult problems, making informed decisions, and delivering excellent solutions.

  • Learn

    With unparalleled access to technical experts, verified real-world solutions, and diverse educational content, Experts Exchange enables personalized development of technology skills.

  • Network

    Experts Exchange gives you the professional exposure and valued relationships key to building the career you want.

Join the Network Today

See Plans and Pricing