I am fairly new to SQL and tying to work out a code. I created the following query and it wont run. The RevenueCD field is located in the [Epic].[Finance].[UBRevenueCode] table and needs to be joined to the Epic.Finance.HospitalTransaction table. The common field in both tables is [UBRevenueCodeID]. When I tried to join them I get the following error messages.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'HospitalTransaction'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'HCPCS_CPT'.
Can anyone tell me why this isn’t working?
, CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
FROM Epic.Finance.HospitalAccount HA
LEFT JOIN Epic.Reference.BenefitPlan PY ON HA.PrimaryPayorID = PY.PayorID
LEFT JOIN Epic.Finance.HospitalTransaction HT ON HA.HospitalAccountID = HT.HospitalAccountID
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN Epic.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
LEFT JOIN [Epic].[Finance].[UBRevenueCode] R ON HT.HospitalTransaction = R.UBRevenueCodeID
DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30'
AND RevenueCD = '0510'
or HCPCS_CPT = 'Q3014'
AND I.IdentityTypeID = '109'
AND HT.HospitalAccountClassDSC = 'Outpatient'
AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
AND HT.TransactionTypeDSC = 'Charge'
AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')