Help with a query

1

Hello
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'.

And here is the code I am trying to run. Thanks in advance for any assistance.

	  PatientMRN
	, PatientNM
    , dischargeDTS
	,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
	, RevenueCD


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

	 

WHERE 
	     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')```
2

Welcome to forum,

as you can see in this join and where clause above it is saying those columns do not exist.

please post the schema of HospitalTransaction?

3

Hi Yosiasz, thanks for the response. I am new to SQL and not sure what you mean by the schema. However, I did correct the join to be....

LEFT JOIN [Epic].[Finance].[UBRevenueCode] R ON HT.UBRevenueCodeID = R.UBRevenueCodeID

so the query now looks like this

	  PatientMRN
	, PatientNM
    , dischargeDTS
	,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
	, RevenueCD


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.UBRevenueCodeID = R.UBRevenueCodeID

	 

WHERE 
	     DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
	  or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30'
	  AND RevenueCD = '0510'
	  or CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.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')```

The problem now is that I get the error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '='.
4

There's a few things going wrong here. First check you or conditions. You should put parenthesis around the ones you want to compare together
( DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30')

second your case statement is all wrong. Should be Case when ... then ...else... end
not sure what you are comparing this to or you're just missing the end?
CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT = 'Q3014'

5

by that I meant to say what are the columns etc. of the tables

6

Hi Mike

The case is also in the select statement were it does the calculation, so I thought if I just used the same case statement but removed the "end" ortion I would be able to get Q3014 only. Instead I am getting all codes not just Q3014

7

can you paste the whole query and error message? Not sure why you'd remove end.

8 
	  PatientMRN
	, PatientNM
    , dischargeDTS
     ,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
	, R.RevenueCD


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.UBRevenueCodeID = R.UBRevenueCodeID

	 

WHERE 
	    (DISCHARGEDTS >='2019-07-01' and DISCHARGEDTS <= '2019-09-30'
	  or DISCHARGEDTS >='2020-07-01' and DISCHARGEDTS <= '2020-09-30')
	  AND (RevenueCD = '0510'
	  or CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.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')```
9

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '='.

10

Line 24 is:

CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT = 'Q3014'

11

what are you attempting to do here?

AND (RevenueCD = '0510'
	  or CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT  = 'Q3014')
12

Still need End

CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END = 'Q3014'