Help with a query

  • wheddingsjr

    SSC Veteran

    Points: 224

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

    Can anyone tell me why this isn’t working?

    SELECT DISTINCT
    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')
  • Cebisa

    SSC Journeyman

    Points: 97

    Does column HCPCS_CPT exist? In your where clause, replace this with the definition you used to calculate it

  • wheddingsjr

    SSC Veteran

    Points: 224

    Thanks Cebisa. I did that and I get this new error message

    Msg 156, Level 15, State 1, Line 24

    Incorrect syntax near the keyword 'AS'. Here is what I did.

    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 END AS 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')
  • wheddingsjr

    SSC Veteran

    Points: 224

    I just removed "END AS HCPCS_CPT" from the line and now I get error message

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near '='.

  • irgp

    Grasshopper

    Points: 11

    Instead of removing "END AS HCPCS_CPT", just remove "AS HCPCS_CPT" - you might want to read up on the syntax of a CASE.. WHEN statement (it always ends with an END and can't have an AS after it )

    Other things you might want to look at:

    Using DISTINCT - remove the DISTINCT and see if you get the same number of records. If not, you are concealing a one-to many join. It's better to understand why so you can improve your coding skills. You might want to read "SQL Code Smells" by Phil Factor - SELECT DISTINCT is in there

    You've used left joins, but some of the joined tables are used in the WHERE clause - that effectively makes them inner joins. Again, it would help you to understand when to use each of the join types (SQL Code Smells again)

    None of the OR expressions is bracketed - I'm not sure whether you intended that, but again you might want to look at how to bracket ANDs and ORs

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply