Hello all
I have this script that i have that had to be altered. Once I altered it I am getting errors. This is the original script:
````SELECT FY
, PatientMRN AS MRN
, Race
INTO #Race
FROM
(SELECT DISTINCT
Race_Num_flg.*
,ROW_NUMBER() OVER (PARTITION BY FY, PatientMRN ORDER BY RACE_num_flg ASC) AS Race_row
,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
--WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
--WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
WHEN PatientRaceDSC IS NULL THEN 'Unknown'
ELSE 'Other' END AS 'RACE'
FROM
(SELECT DISTINCT
CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
,PatientMRN
,PatientRaceDSC
,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
WHEN PatientRaceDSC IS NULL THEN 3
ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/
FROM Epic.Finance.HospitalTransaction HT
LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID
LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
WHERE 1=1
/* DFCI patients only */
AND I.IdentityTypeID = '109'
/* EPIC FY16 and later */
AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
/* Other condistions */
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
) Race_num_flg
) Race_row
WHERE Race_row = 1;
This is the new code:
, I.PatientIdentityID as MRN
, Race
INTO #Race
FROM
(SELECT DISTINCT
Race_Num_flg.*
,ROW_NUMBER() OVER (PARTITION BY FY, I.PatientIdentityID ORDER BY RACE_num_flg ASC) AS Race_row
,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
--WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
--WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
WHEN PatientRaceDSC IS NULL THEN 'Unknown'
ELSE 'Other' END AS 'RACE'
FROM
(SELECT DISTINCT
CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
,I.PatientIdentityID as MRN
,PatientRaceDSC
,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
WHEN PatientRaceDSC IS NULL THEN 3
ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/
FROM Epic.Finance.HospitalTransaction HT
LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID
LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
WHERE 1=1
/* DFCI patients only */
AND I.IdentityTypeID = '109'
/* EPIC FY16 and later */
AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
/* Other condistions */
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
) Race_num_flg
) Race_row
WHERE Race_row = 1
;
The only thing changed is the "PatientMRN as MRN" to "I.PatientIdentityID as MRN" and I get this error:
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "I.PatientIdentityID" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "I.PatientIdentityID" could not be bound.
Any assistance would be greatly appreciated.
Thanks