SQL 2005 - 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: