September 1, 2010 at 9:28 am
I am using sql 2008. This query is designed to get all patients that were readmitted within 30 days of their previous hospital discharge.. The query runs fine if I comment out the 'inner join' and all reference to 'hid1' fields. I am including the query and error message. Any help would be appreciated... thank you
SELECT
cv1.currentlocation,
cv1.clientdisplayname,
hid1.shortname,-- runs ok with this line commmented out
cv1.visitidcode,
cv1.AdmitDtm,
cv1.DischargeDtm,
cv1.dischargedisposition,
cv2.visitidcode as PriorVisitId,
cv2.Admitdtm AS PriorAdmitDate,
MAX(cv2.Dischargedtm) AS PriorDischargeDate,
cv2.currentlocation as Priordischargelocation,
cv2.dischargedisposition as priordischargedispo
FROM cv3clientvisit AS cv1, cv3clientvisit AS cv2 (nolock)
inner join cv3healthissuedeclaration hid1 (nolock) on cv1.clientguid = hid1.clientguid -- runs ok commented out
and cv1.chartguid = hid1.chartguid
WHERE cv2.clientguid = cv1.clientguid
AND cv1.Admitdtm >= cv2.DischargeDtm
and cv1.typecode = 'inpatient'
and cv2.typecode = 'inpatient'
and cv2.dischargedtm >= getdate()-60 -- prior discharge within last 60 days
and hid1.typecode = 'working dx' -- runs ok commented out
GROUP BY cv1.currentlocation,cv1.clientdisplayname,cv1.clientguid, cv1.AdmitDtm, cv1.visitidcode,cv1.
DischargeDtm, cv2.clientguid, cv2.AdmitDtm,cv2.currentlocation,cv2.visitidcode,cv1.dischargedisposition,cv2.dischargedisposition,hid1.shortname
HAVING cv1.AdmitDtm - MAX(cv2.DischargeDtm) <= 30
**** error message ****
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "cv1.clientguid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "cv1.chartguid" could not be bound.
September 1, 2010 at 10:00 am
try this first..
select cv1.chartguid
FROM cv3clientvisit AS cv1
if that works, make sure when you are doing the join the both col are the same type----ie ( varchar= varchar) not (varchar = int) whenyoua re doing the join
September 8, 2010 at 1:54 am
This was removed by the editor as SPAM
September 8, 2010 at 3:10 am
bhutchin (9/1/2010)
I am using sql 2008. This query is designed to get all patients that were readmitted within 30 days of their previous hospital discharge...
Your "key" filter is to include patients which were discharged within the last 60 days. But what happens if they have two (or more) subsequent visits since then? The GROUP BY in your code doesn't sensibly account for this - I think you need to decide which rows you should select for output.
This should help:
SELECT d.clientguid,
d_visitidcode = d.visitidcode,
d_AdmitDtm = d.AdmitDtm,
d_DischargeDtm = d.DischargeDtm,
a_visitidcode = a.visitidcode,
a_AdmitDtm = a.AdmitDtm,
a_DischargeDtm - a.DischargeDtm
FROM cv3clientvisit d -- rows with relevant discharge dates
INNER JOIN cv3clientvisit a -- rows with relevant readmittance gaps
ON a.clientguid = d.clientguid -- same patient
AND a.visitidcode <> d.visitidcode -- different visit records
AND a.typecode = d.typecode -- same typecode
AND DATEDIFF(dd, d.DischargeDtm, a.AdmitDtm) <= 30 -- 30 days apart, or less
WHERE d.dischargedtm >= getdate()-60 -- change to time-stripped datetime variable
AND d.typecode = 'inpatient'
ORDER BY d.clientguid, d.visitidcode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply