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 157
Conversion failed when converting the varchar value '6.2' to data type int.
Looks like its coming from the obs.OBSVALUE field.
When I used this query:
select * from obs where ObsValue = '6.2' and HDID IN (53,54)
I got back several rows.
So I think this is whats causing my issue, since my original error message was "Conversion failed when converting the varchar value '6.2' to data type int."
Msg 245, Level 16, State 1, Line 157
Conversion failed when converting the varchar value '6.2' to data type int.
Looks like its coming from the obs.OBSVALUE field.
When I used this query:
select * from obs where ObsValue = '6.2' and HDID IN (53,54)
I got back several rows.
So I think this is whats causing my issue, since my original error message was "Conversion failed when converting the varchar value '6.2' to data type int."
Code:
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'
--select * from obs where ObsValue = '6.2' and HDID IN (53,54)
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