How do I add a new column to an existing SQL Query that has multiple unions

wheddingsjr 1 Reputation point
2020-09-08T16:09:19.147+00:00

HI all

I inherited an SQL query that contains multiple unions. I was asked to add a new column to the query but when I did I keep getting the below error message:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

The new column name is "Loc.RevenueLocationNM" and the code is:

SELECT CH.*,
    FLG.ActivityType,
    FLG.TxType,
    FLG.Infusion_Group,
    CASE WHEN NEW.MRN IS NOT NULL THEN 'New'
    ELSE 'Established'
    END AS FY_New_Established,
        NEW.New_Patient_Date,
    CASE WHEN CH.HCPCS_CPT IN ('92506','92507','92526','92597','92610','96040','97802','97803','99024','99071','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215','99241','99242','99243','99244','99245','99284','99285','99291','99354','99355','99363','99364','99396','99397','G0101','G0463')
    AND CH.DetailTotalChargeAMT <> 0
    THEN CH.Units ELSE 0 END AS Exams

    ,CASE WHEN CH.HCPCS_CPT IN ('36430','96361','96400','96413','96521','C9003',    'C9132',    'C9240',    'C9280',    'C9399',    'C9449',    'C9483',    'J0171',    'J0295',    'J0574',    'J0690',    'J0743',    'J0886',    'J1080',    'J1250',    'J1440',    'J1562',    'J1630',    'J1750',    'J1940',    'J2210',    'J2354',    'J2505',    'J2710',    'J2796',    'J3010',    'J3370',    'J3487',    'J7070',    'J7195',    'J7507',    'J7520',    'J7644',    'J8610',    'J9020',    'J9040',    'J9070',    'J9160',    '36514',    '96366',    '96402',    '96415',    '96523',    'C9024',    'C9205',    'C9245',    'C9285',    'C9414',    'C9455',    'C9491',    'J0202',    'J0360',    'J0585',    'J0694',    'J0770',    'J0895',    'J1120',    'J1290',    'J1442',    'J1566',    'J1644',    'J1785',    'J1953',    'J2248',    'J2370',    'J2543',    'J2765',    'J2820',    'J3121',    'J3411',    'J3489',    'J7131',    'J7197',    'J7510',    'J7527',    'J8501',    'J8999',    'J9023',    'J9042',    'J9098',    'J9171',    '96375',    '96411',    '96450',    'C8955',    'C9127',    'C9235',    'C9273',    'C9295',    'C9441',    'C9477',    'J0150',    'J0289',    'J0571',    'J0640',    'J0735',    'J0882',    'J1055',    'J1205',    'J1438',    'J1459',    'J1610',    'J1743',    'J1930',    'J2175',    'J2310',    'J2501',    'J2700',    'J2792',    'J2930',    'J3315',    'J3475',    'J7050',    'J7192',    'J7504',    'J7517',    'J7626',    'J8560',    'J9017',    'J9035',    'J9060',    'J9151',    'J9190',    '90761',    '96368',    '96406',    '96417',    '96542',    'C9027',    'C9213',    'C9259',    'C9289',    'C9424',    'C9472',    'J0129',    'J0256',    'J0461',    'J0610',    'J0696',    'J0834',    'J1020',    'J1165',    'J1325',    'J1447',    'J1569',    'J1650',    'J1815',    'J2001',    'J2270',    'J2405',    'J2550',    'J2783',    'J2860',    'J3240',    'J3430',    'J3590',    'J7185',    'J7199',    'J7512',    'J7611',    'J8520',    'J9001',    'J9027',    'J9045',    'J9120',    'J9178',    '90775',    '96372',    '96409',    '96445',    'C8953',    'C9030',    'C9215',    'C9265',    'C9292',    'C9429',    'C9474',    'J0132',    'J0282',    'J0515',    'J0636',    'J0702',    'J0878',    'J1050',    'J1190',    'J1364',    'J1453',    'J1572',    'J1720',    'J1833',    'J2060',    'J2280',    'J2430',    'J2562',    'J2790',    'J2916',    'J3262',    'J3470',    'J7040',    'J7189',    'J7205',    'J7515',    'J7614',    'J8530',    'J9010',    'J9033',    'J9050',    'J9145',    'J9181',    'J9202',    'J9203',    'J9205',    'J9206',    'J9207',    'J9208',    'J9209',    'J9211',    'J9213',    'J9214',    'J9216',    'J9217',    'J9228',    'J9230',    'J9245',    'J9250',    'J9260',    'J9261',    'J9263',    'J9264',    'J9265',    'J9266',    'J9267',    'J9268',    'J9271',    'J9280',    'J9285',    'J9293',    'J9295',    'J9299',    'J9300',    'J9301',    'J9302',    'J9303',    'J9305',    'J9306',    'J9307',    'J9308',    'J9310',    'J9315',    'J9320',    'J9325',    'J9328',    '36511',    '96365',    '96401',    '96414',    '96522',    'C9021',    'C9136',    'C9243',    'C9284',    'C9410',    'C9453',    'C9485',    'J0180',    'J0330',    'J0583',    'J0692',    'J0744',    'J0894',    'J1100',    'J1265',    'J1441',    'J1563',    'J1642',    'J1756',    'J1950',    'J2212',    'J2355',    'J2540',    'J2720',    'J2805',    'J3030',    'J3410',    'J3488',    'J7120',    'J7196',    'J7509',    'J7525',    'J8499',    'J8700',    'J9022',    'J9041',    'J9093',    'J9170',    '90760',    '96367',    '96405',    '96416',    '96530',    'C9025',    'C9207',    'C9254',    'C9287',    'C9421',    'C9467',    'C9492',    'J0207',    'J0456',    'J0594',    'J0695',    'J0780',    'J0897',    'J1160',    'J1300',    'J1446',    'J1567',    'J1645',    'J1786',    'J1956',    'J2250',    'J2400',    'J2545',    'J2780',    'J2850',    'J3230',    'J3420',    'J3490',    'J7178',    'J7198',    'J7511',    'J7608',    'J8515',    'J9000',    'J9025',    'J9043',    'J9100',    'J9176',    '96376',    '96412',    '96520',    'C8957',    'C9131',    'C9239',    'C9276',    'C9296',    'C9442',    'C9480',    'J0153',    'J0290',    'J0572',    'J0670',    'J0740',    'J0885',    'J1071',    'J1230',    'J1439',    'J1561',    'J1626',    'J1745',    'J1931',    'J2185',    'J2353',    'J2504',    'J2704',    'J2795',    'J2997',    'J3360',    'J3480',    'J7060',    'J7194',    'J7506',    'J7518',    'J7639',    'J8600',    'J9019',    'J9039',    'J9065',    'J9155',    'J9201',    '90765',    '96369',    '96408',    '96425',    '96549',    'C9028',    'C9214',    'C9260',    'C9290',    'C9426',    'C9473',    'J0131',    'J0280',    'J0480',    'J0630',    'J0698',    'J0835',    'J1030',    'J1170',    'J1335',    'J1450',    'J1570',    'J1652',    'J1817',    'J2020',    'J2274',    'J2425',    'J2560',    'J2785',    'J2912',    'J3243',    'J3465',    'J7030',    'J7187',    'J7200',    'J7513',    'J7613',    'J8521',    'J9002',    'J9032',    'J9047',    'J9130',    'J9179',    '96360',    '96374',    '96410',    '96446',    'C8954',    'C9113',    'C9218',    'C9272',    'C9293',    'C9433',    'C9476',    'J0133',    'J0285',    'J0561',    'J0637',    'J0713',    'J0881',    'J1051',    'J1200',    'J1410',    'J1455',    'J1580',    'J1740',    'J1885',    'J2150',    'J2300',    'J2469',    'J2597',    'J2791',    'J2920',    'J3301',    'J3473',    'J7042',    'J7190',    'J7502',    'J7516',    'J7620',    'J8540',    'J9015',    'J9034',    'J9055',    'J9150',    'J9185',    'J9330',    'J9340',    'J9350',    'J9351',    'J9352',    'J9354',    'J9355',    'J9360',    'J9370',    'J9390',    'J9395',    'J9400',    'J9600',    'J9999',    'P9011',    'P9012',    'P9016',    'P9017',    'P9019',    'P9022',    'P9033',    'P9034',    'P9035',    'P9037',    'P9038',    'P9040',    'P9041',    'P9045',    'P9046',    'P9047',    'P9052',    'P9053',    'P9054',    'P9057',    'P9059',    'Q0083',    'Q0084',    'Q0136',    'Q0137',    'Q0138',    'Q0139',    'Q0161',    'Q0162',    'Q0163',    'Q0164',    'Q0165',    'Q0166',    'Q0167',    'Q0168',    'Q0169',    'Q0170',    'Q0172',    'Q0173',    'Q0175',    'Q0177',    'Q0179',    'Q2022',    'Q2043',    'Q2048',    'Q2049',    'Q2050',    'Q2051',    'Q4081',    'Q4116',    'Q9970',    'Q9975',    'S0020',    'S0028',    'S0030',    'S0039',    'S0073',    'S0077',    'S0080',    'S0108',    'S0119',    'S0145',    'S0148',    'S0160',    'S0164',    'S0171',    'S0172',    'S0178',    'S0179',    'S0183',    'S5010') 
    AND CH.DepartmentDSC IN ('BLC','CRC','CRP','D10','D11','D1B','DF CENTRAL PHARM LNH','DF CENTRAL PHARM MIL','DF CENTRAL PHARM SS','DF CENTRAL PHARMACY','DF INF IN RAD ONC SS','DF INFUSION LNH','DF INFUSION MIL','DF INFUSION ROOM SS','DF INFUSION SE','DF INFUSION YAWKEY 10','DF INFUSION YAWKEY 11','DF INFUSION YAWKEY 6','DF INFUSION YAWKEY 7','DF INFUSION YAWKEY 8','DF INFUSION YAWKEY 9','DF LP INF IN RAD ONC','DF PEDI INFUSION','DF PEDI PHLEBOTOMY','DF PHARMACY SE','FFS','FIN','FSU','LFS','LIN','LNH PHARMACY','LNHRX','LPI','MILFORD PHARMACY','MIN','MRX','N11','PHA','PIR','SE PHARMACY','SEI','SIN','SIR','SS PHARMACY','SSRX','SWI','Y10','Y11','Y6I','Y7I','Y8I','Y9I')                    
    THEN 1 ELSE 0 END AS InfusionFLG

    ,CASE WHEN(
    CH.HCPCS_CPT IN ('31575', '77014', '77280', '77290', '77293', '77295', '77300', '77301', '77306', '77307', '77315', '77318', '77321', '77331', '77332', '77334', '77336', '77338', '77370', '77373', '77385', '77386', '77387', '77402', '77403', '77404', '77412', '77413', '77414', '77417', '77418', '77421', '77470', '92511', '96401', '97802', '97803', '99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99221', '99222', '99223', '99231', '99232', '99233', '99241', '99242', '99243', '99244', '99245', '99251', '99252', '99253', '99254', '99255', '99354', '99355', 'G0251')
    OR CH.ActivityCD IN ('76000771', '35000006', '33000001', '33000007', '33000009', '33000010', '33000012', '33000089', '33000102', '33000103', '33000022', '33000024', '33000025', '33000016', '33000026', '33000027', '33000106', '33000107', '33000108', '33000109', '33000111', '33000034', '33000035', '33000113', '33000042', '33000043', '33000045', '33000046', '33000047', '33000054', '94000019', '49000096', '49000097', '49000164', '49000165', '51000544', '51000369', '51000432', '51000430', '51000430', '51000432', '51000432', '51000440', '51000503', '51000504', '51000490', '51000510', '51000499', '51000514', '51000487', '51000500', '51000485', '51000513', '51000509', '51000497', '51000433', '51000435', '51000435', '51000436', '51000544', '51000437', '94000018', '99900016', '99900016', '51000369', '51000431', '51000433', '33000028', '51000480', '33000091', '51000481', '98300744', '98300682', '33000017', '33000021', '33000097', '51000441', '99900168', '99900180', '99900187', '51000428', '51000476', '51000477', '98300748', '98300749', '98300745', '98300750', '51000370', '98300753', '98300682', '98300683', '51000482')
    )
    AND CH.DepartmentDSC IN ('RTH','SRT','LPT','DF RADIATION ONCOLOGY','DF RAD ONCOLOGY SS','DF RAD ONCOLOGY LP')
    THEN 1 ELSE 0 END AS RadiationFLG



FROM
(
        --EPSI (for historical data)
        SELECT 
        CAST(EE.DischargeDTS AS DATE) AS DischargeDT
        ,CASE WHEN Month(EE.DischargeDTS) > 9 THEN YEAR(EE.DischargeDTS) + 1 ELSE YEAR(EE.DischargeDTS) END AS FY
        ,EC.UserField01TXT AS DepartmentDSC
        ,EC.HCPCS AS HCPCS_CPT
        ,EC.ActivityCD
        ,CAST(EC.UserField16NBR AS INT) AS Units
        ,EE.MRN
        ,RIGHT(EE.PatientAccountID,10) AS HospitalAccountID
        ,EC.DetailTotalChargeAMT

          FROM [EPSI].Cost.OutpatientCharge EC
          LEFT JOIN [EPSI].Cost.PatientEncounter EE ON EC.PatientAccountID = EE.PatientAccountID
          --EPSI FY15 and before
          WHERE CASE WHEN Month(EE.DischargeDTS) > 9 THEN YEAR(EE.DischargeDTS) + 1 ELSE YEAR(EE.DischargeDTS) END <= 2015
                  AND EC.UserField01TXT NOT IN ('BMV','DF MAMMOGRAPHY VAN')

                UNION ALL

        --EPIC 
        SELECT 
        CAST(HA.DischargeDTS AS DATE) AS DischargeDT
        ,CASE WHEN Month(HA.DischargeDTS) > 9 THEN YEAR(HA.DischargeDTS) + 1 ELSE YEAR(HA.DischargeDTS) END AS FY
        ,HT.DepartmentDSC
        ,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
        ,P.ProcedureCD AS ActivityCD
        ,CASE WHEN HT.ChargeModifierListTXT LIKE '%DFP%' THEN 0 ELSE CAST(HT.TransactionCNT AS INT) END AS Units
        ,I.PatientIdentityID AS MRN
        ,HA.HospitalAccountID
        ,HT.TransactionAMT AS DetailTotalChargeAMT

          FROM [Epic].[Finance].[HospitalTransaction] HT
          LEFT JOIN [Epic].[Finance].[HospitalAccount] HA ON HA.HospitalAccountID = HT.HospitalAccountID
            LEFT JOIN EPIC.Reference.[Procedure] P ON HT.ProcedureID = P.ProcedureID
          LEFT JOIN EPIC.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
        LEFT JOIN EPIC.Patient.[Identity] I ON I.PatientID = HA.PatientID

          WHERE 1=1
          --Only DFCI MRNS
         AND I.IdentityTypeID = '109'
          --EPIC FY16 and later
          AND CASE WHEN Month(HA.DischargeDTS) > 9 THEN YEAR(HA.DischargeDTS) + 1 ELSE YEAR(HA.DischargeDTS) END >2015
          --Only Outpatient
          AND HT.HospitalAccountClassDSC = 'Outpatient'
          --Only DFCI
          AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
          AND HT.TransactionTypeDSC = 'Charge'
                  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')

  ) CH



LEFT JOIN


(
SELECT 
D.FY
,D.MRN
,CASE WHEN D.Infusion = 1 OR D.Radiation = 1 THEN 'Actively Treated'
WHEN D.Exams > 2 THEN 'Actively Followed'
ELSE 'Consult'
END
AS ActivityType
,CASE WHEN D.Infusion = 1 AND D.Radiation <> 1 THEN 'Infused'
WHEN D.Infusion <> 1 AND D.Radiation = 1 THEN 'Radiated'
WHEN D.Infusion = 1 AND D.Radiation = 1 THEN 'Infused + Radiated'
ELSE 'Non-Treated'
END AS TxType
,D.Infusion_Group

FROM
(
SELECT 
CASE WHEN Month(UN.DischargeDT) > 9 THEN YEAR(UN.DischargeDT) + 1 ELSE YEAR(UN.DischargeDT) END AS FY
,UN.MRN

,SUM(CASE WHEN
UN.HCPCS_CPT IN ('92506','92507','92526','92597','92610','96040','97802','97803','99024','99071','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215','99241','99242','99243','99244','99245','99284','99285','99291','99354','99355','99363','99364','99396','99397','G0101','G0463')
AND UN.DetailTotalChargeAMT <> 0
THEN UN.Units ELSE 0 END) AS Exams

,MAX(CASE WHEN UN.HCPCS_CPT IN ('36430','96361','96400','96413','96521','C9003',    'C9132',    'C9240',    'C9280',    'C9399',    'C9449',    'C9483',    'J0171',    'J0295',    'J0574',    'J0690',    'J0743',    'J0886',    'J1080',    'J1250',    'J1440',    'J1562',    'J1630',    'J1750',    'J1940',    'J2210',    'J2354',    'J2505',    'J2710',    'J2796',    'J3010',    'J3370',    'J3487',    'J7070',    'J7195',    'J7507',    'J7520',    'J7644',    'J8610',    'J9020',    'J9040',    'J9070',    'J9160',    '36514',    '96366',    '96402',    '96415',    '96523',    'C9024',    'C9205',    'C9245',    'C9285',    'C9414',    'C9455',    'C9491',    'J0202',    'J0360',    'J0585',    'J0694',    'J0770',    'J0895',    'J1120',    'J1290',    'J1442',    'J1566',    'J1644',    'J1785',    'J1953',    'J2248',    'J2370',    'J2543',    'J2765',    'J2820',    'J3121',    'J3411',    'J3489',    'J7131',    'J7197',    'J7510',    'J7527',    'J8501',    'J8999',    'J9023',    'J9042',    'J9098',    'J9171',    '96375',    '96411',    '96450',    'C8955',    'C9127',    'C9235',    'C9273',    'C9295',    'C9441',    'C9477',    'J0150',    'J0289',    'J0571',    'J0640',    'J0735',    'J0882',    'J1055',    'J1205',    'J1438',    'J1459',    'J1610',    'J1743',    'J1930',    'J2175',    'J2310',    'J2501',    'J2700',    'J2792',    'J2930',    'J3315',    'J3475',    'J7050',    'J7192',    'J7504',    'J7517',    'J7626',    'J8560',    'J9017',    'J9035',    'J9060',    'J9151',    'J9190',    '90761',    '96368',    '96406',    '96417',    '96542',    'C9027',    'C9213',    'C9259',    'C9289',    'C9424',    'C9472',    'J0129',    'J0256',    'J0461',    'J0610',    'J0696',    'J0834',    'J1020',    'J1165',    'J1325',    'J1447',    'J1569',    'J1650',    'J1815',    'J2001',    'J2270',    'J2405',    'J2550',    'J2783',    'J2860',    'J3240',    'J3430',    'J3590',    'J7185',    'J7199',    'J7512',    'J7611',    'J8520',    'J9001',    'J9027',    'J9045',    'J9120',    'J9178',    '90775',    '96372',    '96409',    '96445',    'C8953',    'C9030',    'C9215',    'C9265',    'C9292',    'C9429',    'C9474',    'J0132',    'J0282',    'J0515',    'J0636',    'J0702',    'J0878',    'J1050',    'J1190',    'J1364',    'J1453',    'J1572',    'J1720',    'J1833',    'J2060',    'J2280',    'J2430',    'J2562',    'J2790',    'J2916',    'J3262',    'J3470',    'J7040',    'J7189',    'J7205',    'J7515',    'J7614',    'J8530',    'J9010',    'J9033',    'J9050',    'J9145',    'J9181',    'J9202',    'J9203',    'J9205',    'J9206',    'J9207',    'J9208',    'J9209',    'J9211',    'J9213',    'J9214',    'J9216',    'J9217',    'J9228',    'J9230',    'J9245',    'J9250',    'J9260',    'J9261',    'J9263',    'J9264',    'J9265',    'J9266',    'J9267',    'J9268',    'J9271',    'J9280',    'J9285',    'J9293',    'J9295',    'J9299',    'J9300',    'J9301',    'J9302',    'J9303',    'J9305',    'J9306',    'J9307',    'J9308',    'J9310',    'J9315',    'J9320',    'J9325',    'J9328',    '36511',    '96365',    '96401',    '96414',    '96522',    'C9021',    'C9136',    'C9243',    'C9284',    'C9410',    'C9453',    'C9485',    'J0180',    'J0330',    'J0583',    'J0692',    'J0744',    'J0894',    'J1100',    'J1265',    'J1441',    'J1563',    'J1642',    'J1756',    'J1950',    'J2212',    'J2355',    'J2540',    'J2720',    'J2805',    'J3030',    'J3410',    'J3488',    'J7120',    'J7196',    'J7509',    'J7525',    'J8499',    'J8700',    'J9022',    'J9041',    'J9093',    'J9170',    '90760',    '96367',    '96405',    '96416',    '96530',    'C9025',    'C9207',    'C9254',    'C9287',    'C9421',    'C9467',    'C9492',    'J0207',    'J0456',    'J0594',    'J0695',    'J0780',    'J0897',    'J1160',    'J1300',    'J1446',    'J1567',    'J1645',    'J1786',    'J1956',    'J2250',    'J2400',    'J2545',    'J2780',    'J2850',    'J3230',    'J3420',    'J3490',    'J7178',    'J7198',    'J7511',    'J7608',    'J8515',    'J9000',    'J9025',    'J9043',    'J9100',    'J9176',    '96376',    '96412',    '96520',    'C8957',    'C9131',    'C9239',    'C9276',    'C9296',    'C9442',    'C9480',    'J0153',    'J0290',    'J0572',    'J0670',    'J0740',    'J0885',    'J1071',    'J1230',    'J1439',    'J1561',    'J1626',    'J1745',    'J1931',    'J2185',    'J2353',    'J2504',    'J2704',    'J2795',    'J2997',    'J3360',    'J3480',    'J7060',    'J7194',    'J7506',    'J7518',    'J7639',    'J8600',    'J9019',    'J9039',    'J9065',    'J9155',    'J9201',    '90765',    '96369',    '96408',    '96425',    '96549',    'C9028',    'C9214',    'C9260',    'C9290',    'C9426',    'C9473',    'J0131',    'J0280',    'J0480',    'J0630',    'J0698',    'J0835',    'J1030',    'J1170',    'J1335',    'J1450',    'J1570',    'J1652',    'J1817',    'J2020',    'J2274',    'J2425',    'J2560',    'J2785',    'J2912',    'J3243',    'J3465',    'J7030',    'J7187',    'J7200',    'J7513',    'J7613',    'J8521',    'J9002',    'J9032',    'J9047',    'J9130',    'J9179',    '96360',    '96374',    '96410',    '96446',    'C8954',    'C9113',    'C9218',    'C9272',    'C9293',    'C9433',    'C9476',    'J0133',    'J0285',    'J0561',    'J0637',    'J0713',    'J0881',    'J1051',    'J1200',    'J1410',    'J1455',    'J1580',    'J1740',    'J1885',    'J2150',    'J2300',    'J2469',    'J2597',    'J2791',    'J2920',    'J3301',    'J3473',    'J7042',    'J7190',    'J7502',    'J7516',    'J7620',    'J8540',    'J9015',    'J9034',    'J9055',    'J9150',    'J9185',    'J9330',    'J9340',    'J9350',    'J9351',    'J9352',    'J9354',    'J9355',    'J9360',    'J9370',    'J9390',    'J9395',    'J9400',    'J9600',    'J9999',    'P9011',    'P9012',    'P9016',    'P9017',    'P9019',    'P9022',    'P9033',    'P9034',    'P9035',    'P9037',    'P9038',    'P9040',    'P9041',    'P9045',    'P9046',    'P9047',    'P9052',    'P9053',    'P9054',    'P9057',    'P9059',    'Q0083',    'Q0084',    'Q0136',    'Q0137',    'Q0138',    'Q0139',    'Q0161',    'Q0162',    'Q0163',    'Q0164',    'Q0165',    'Q0166',    'Q0167',    'Q0168',    'Q0169',    'Q0170',    'Q0172',    'Q0173',    'Q0175',    'Q0177',    'Q0179',    'Q2022',    'Q2043',    'Q2048',    'Q2049',    'Q2050',    'Q2051',    'Q4081',    'Q4116',    'Q9970',    'Q9975',    'S0020',    'S0028',    'S0030',    'S0039',    'S0073',    'S0077',    'S0080',    'S0108',    'S0119',    'S0145',    'S0148',    'S0160',    'S0164',    'S0171',    'S0172',    'S0178',    'S0179',    'S0183',    'S5010') 
AND UN.DepartmentDSC IN ('BLC','CRC','CRP','D10','D11','D1B','DF CENTRAL PHARM LNH','DF CENTRAL PHARM MIL','DF CENTRAL PHARM SS','DF CENTRAL PHARMACY','DF INF IN RAD ONC SS','DF INFUSION LNH','DF INFUSION MIL','DF INFUSION ROOM SS','DF INFUSION SE','DF INFUSION YAWKEY 10','DF INFUSION YAWKEY 11','DF INFUSION YAWKEY 6','DF INFUSION YAWKEY 7','DF INFUSION YAWKEY 8','DF INFUSION YAWKEY 9','DF LP INF IN RAD ONC','DF PEDI INFUSION','DF PEDI PHLEBOTOMY','DF PHARMACY SE','FFS','FIN','FSU','LFS','LIN','LNH PHARMACY','LNHRX','LPI','MILFORD PHARMACY','MIN','MRX','N11','PHA','PIR','SE PHARMACY','SEI','SIN','SIR','SS PHARMACY','SSRX','SWI','Y10','Y11','Y6I','Y7I','Y8I','Y9I')                    
THEN 1 ELSE 0 END) AS Infusion

,MAX(CASE WHEN(
UN.HCPCS_CPT IN ('31575', '77014', '77280', '77290', '77293', '77295', '77300', '77301', '77306', '77307', '77315', '77318', '77321', '77331', '77332', '77334', '77336', '77338', '77370', '77373', '77385', '77386', '77387', '77402', '77403', '77404', '77412', '77413', '77414', '77417', '77418', '77421', '77470', '92511', '96401', '97802', '97803', '99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', '99214', '99215', '99221', '99222', '99223', '99231', '99232', '99233', '99241', '99242', '99243', '99244', '99245', '99251', '99252', '99253', '99254', '99255', '99354', '99355', 'G0251')
OR UN.ActivityCD IN ('76000771', '35000006', '33000001', '33000007', '33000009', '33000010', '33000012', '33000089', '33000102', '33000103', '33000022', '33000024', '33000025', '33000016', '33000026', '33000027', '33000106', '33000107', '33000108', '33000109', '33000111', '33000034', '33000035', '33000113', '33000042', '33000043', '33000045', '33000046', '33000047', '33000054', '94000019', '49000096', '49000097', '49000164', '49000165', '51000544', '51000369', '51000432', '51000430', '51000430', '51000432', '51000432', '51000440', '51000503', '51000504', '51000490', '51000510', '51000499', '51000514', '51000487', '51000500', '51000485', '51000513', '51000509', '51000497', '51000433', '51000435', '51000435', '51000436', '51000544', '51000437', '94000018', '99900016', '99900016', '51000369', '51000431', '51000433', '33000028', '51000480', '33000091', '51000481', '98300744', '98300682', '33000017', '33000021', '33000097', '51000441', '99900168', '99900180', '99900187', '51000428', '51000476', '51000477', '98300748', '98300749', '98300745', '98300750', '51000370', '98300753', '98300682', '98300683', '51000482')
)
AND UN.DepartmentDSC IN ('RTH','SRT','LPT','DF RADIATION ONCOLOGY','DF RAD ONCOLOGY SS','DF RAD ONCOLOGY LP')
THEN 1 ELSE 0 END) AS "Radiation"

,CASE 
    WHEN SUM(BIO_FLG) > 0 AND SUM(CYTO_FLG) = 0 THEN 'BIOLOGICS'
    WHEN SUM(CYTO_FLG) > 0 AND SUM(BIO_FLG)= 0 THEN 'CHEMOTHERAPY'
    WHEN SUM(CYTO_FLG) > 0 AND SUM(BIO_FLG) > 0 THEN 'COMBINATIONAL' 
    WHEN SUM(HEM_FLG) > 0 THEN 'HEMATOPOETIC'
    ELSE 'OTHER' END AS Infusion_Group

FROM
(
        --EPSI (for historical data)
        SELECT 
        CAST(EE.DischargeDTS AS DATE) AS DischargeDT
        ,EC.UserField01TXT AS DepartmentDSC
        ,EC.HCPCS AS HCPCS_CPT
        ,EC.ActivityCD
        ,CAST(EC.UserField16NBR AS INT) AS Units
        ,EE.MRN
        ,RIGHT(EE.PatientAccountID,10) AS HospitalAccountID
        ,EC.DetailTotalChargeAMT
        --Added infusion type flag
        ,CASE WHEN
        EC.HCPCS IN ('C9024',   'C9030',    'C9127',    'C9207',    'C9213',    'C9215',    'C9218',    'C9235',    'C9239',    'C9240',    'C9243',    'C9276',    'C9280',    'C9289',    'C9410',    'C9414',    'C9421',    'C9424',    'C9426',    'C9429',    'C9433',    'C9453',    'C9474',    'C9480',    'J0594',    'J0894',    'J7502',    'J7515',    'J8520',    'J8521',    'J8530',    'J8560',    'J8600',    'J8610',    'J8700',    'J8999',    'J9000',    'J9001',    'J9002',    'J9015',    'J9017',    'J9020',    'J9025',    'J9027',    'J9033',    'J9040',    'J9041',    'J9043',    'J9045',    'J9050',    'J9060',    'J9065',    'J9070',    'J9093',    'J9098',    'J9100',    'J9120',    'J9130',    'J9150',    'J9151',    'J9170',    'J9171',    'J9178',    'J9179',    'J9181',    'J9185',    'J9190',    'J9201',    'J9205',    'J9206',    'J9207',    'J9208',    'J9209',    'J9211',    'J9230',    'J9245',    'J9250',    'J9260',    'J9263',    'J9264',    'J9265',    'J9266',    'J9267',    'J9280',    'J9293',    'J9305',    'J9328',    'J9330',    'J9340',    'J9350',    'J9351',    'J9352',    'J9360',    'J9370',    'J9390',    'J9999',    'Q2048',    'Q2049',    'Q2050',    'S0172',    'S0178')
        THEN 1 ELSE 0 END AS CYTO_FLG
        ,CASE WHEN 
        EC.HCPCS IN ('C9021',   'C9025',    'C9027',    'C9028',    'C9131',    'C9214',    'C9260',    'C9272',    'C9273',    'C9284',    'C9287',    'C9292',    'C9296',    'C9449',    'C9455',    'C9467',    'C9472',    'C9473',    'C9476',    'C9477',    'C9483',    'C9485',    'C9491',    'C9492',    'J0202',    'J0480',    'J0882',    'J0886',    'J0897',    'J1300',    'J1438',    'J1439',    'J1459',    'J1561',    'J1566',    'J1567',    'J1569',    'J1572',    'J1745',    'J1786',    'J2355',    'J2501',    'J2791',    'J3262',    'J3590',    'J9010',    'J9022',    'J9023',    'J9034',    'J9035',    'J9039',    'J9042',    'J9055',    'J9145',    'J9176',    'J9203',    'J9213',    'J9214',    'J9216',    'J9228',    'J9271',    'J9285',    'J9295',    'J9299',    'J9301',    'J9302',    'J9303',    'J9306',    'J9308',    'J9310',    'J9312',    'J9325',    'J9354',    'J9355',    'J9400',    'Q0136',    'Q2043',    'Q4081',    'S0145')
        THEN 1 ELSE 0 END AS BIO_FLG
        ,CASE WHEN 
        EC.HCPCS IN ('J0881',   'J0885',    'J1440',    'J1441',    'J1442',    'J1446',    'J1447',    'J2505',    'J2562',    'J2796',    'J2820')
        THEN 1 ELSE 0 END AS HEM_FLG

          FROM [EPSI].Cost.OutpatientCharge EC
          LEFT JOIN [EPSI].Cost.PatientEncounter EE ON EC.PatientAccountID = EE.PatientAccountID
          --EPSI FY15 and before
          WHERE CASE WHEN Month(EE.DischargeDTS) > 9 THEN YEAR(EE.DischargeDTS) + 1 ELSE YEAR(EE.DischargeDTS) END <= 2015

        UNION ALL

        --EPIC
        SELECT 
        CAST(HA.DischargeDTS AS DATE) AS DischargeDT
        ,HT.DepartmentDSC
        ,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
        ,P.ProcedureCD AS ActivityCD
        ,CASE WHEN HT.ChargeModifierListTXT LIKE '%DFP%' THEN 0 ELSE CAST(HT.TransactionCNT AS INT) END AS Units
        ,I.PatientIdentityID AS MRN
        ,HA.HospitalAccountID
        ,HT.TransactionAMT AS DetailTotalChargeAMT
        --Added infusion type flag
        ,CASE WHEN
        CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END IN ('C9024', 'C9030',    'C9127',    'C9207',    'C9213',    'C9215',    'C9218',    'C9235',    'C9239',    'C9240',    'C9243',    'C9276',    'C9280',    'C9289',    'C9410',    'C9414',    'C9421',    'C9424',    'C9426',    'C9429',    'C9433',    'C9453',    'C9474',    'C9480',    'J0594',    'J0894',    'J7502',    'J7515',    'J8520',    'J8521',    'J8530',    'J8560',    'J8600',    'J8610',    'J8700',    'J8999',    'J9000',    'J9001',    'J9002',    'J9015',    'J9017',    'J9020',    'J9025',    'J9027',    'J9033',    'J9040',    'J9041',    'J9043',    'J9045',    'J9050',    'J9060',    'J9065',    'J9070',    'J9093',    'J9098',    'J9100',    'J9120',    'J9130',    'J9150',    'J9151',    'J9170',    'J9171',    'J9178',    'J9179',    'J9181',    'J9185',    'J9190',    'J9201',    'J9205',    'J9206',    'J9207',    'J9208',    'J9209',    'J9211',    'J9230',    'J9245',    'J9250',    'J9260',    'J9263',    'J9264',    'J9265',    'J9266',    'J9267',    'J9280',    'J9293',    'J9305',    'J9328',    'J9330',    'J9340',    'J9350',    'J9351',    'J9352',    'J9360',    'J9370',    'J9390',    'J9999',    'Q2048',    'Q2049',    'Q2050',    'S0172',    'S0178')
        THEN 1 ELSE 0 END AS CYTO_FLG
        ,CASE WHEN 
        CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END IN ('C9021', 'C9025',    'C9027',    'C9028',    'C9131',    'C9214',    'C9260',    'C9272',    'C9273',    'C9284',    'C9287',    'C9292',    'C9296',    'C9449',    'C9455',    'C9467',    'C9472',    'C9473',    'C9476',    'C9477',    'C9483',    'C9485',    'C9491',    'C9492',    'J0202',    'J0480',    'J0882',    'J0886',    'J0897',    'J1300',    'J1438',    'J1439',    'J1459',    'J1561',    'J1566',    'J1567',    'J1569',    'J1572',    'J1745',    'J1786',    'J2355',    'J2501',    'J2791',    'J3262',    'J3590',    'J9010',    'J9022',    'J9023',    'J9034',    'J9035',    'J9039',    'J9042',    'J9055',    'J9145',    'J9176',    'J9203',    'J9213',    'J9214',    'J9216',    'J9228',    'J9271',    'J9285',    'J9295',    'J9299',    'J9301',    'J9302',    'J9303',    'J9306',    'J9308',    'J9310',    'J9312',    'J9325',    'J9354',    'J9355',    'J9400',    'Q0136',    'Q2043',    'Q4081',    'S0145')
        THEN 1 ELSE 0 END AS BIO_FLG
        ,CASE WHEN 
        CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END IN ('J0881', 'J0885',    'J1440',    'J1441',    'J1442',    'J1446',    'J1447',    'J2505',    'J2562',    'J2796',    'J2820')
        THEN 1 ELSE 0 END AS HEM_FLG

          FROM [Epic].[Finance].[HospitalTransaction] HT
          LEFT JOIN [Epic].[Finance].[HospitalAccount] HA ON HA.HospitalAccountID = HT.HospitalAccountID
            LEFT JOIN EPIC.Reference.[Procedure] P ON HT.ProcedureID = P.ProcedureID
          LEFT JOIN EPIC.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
        LEFT JOIN EPIC.Patient.[Identity] I ON I.PatientID = HA.PatientID

          WHERE 1=1
          --Only DFCI MRNS
         AND I.IdentityTypeID = '109'
          --EPIC FY16 and later
          AND CASE WHEN Month(HA.DischargeDTS) > 9 THEN YEAR(HA.DischargeDTS) + 1 ELSE YEAR(HA.DischargeDTS) END >2015
          --Only Outpatient
          AND HT.HospitalAccountClassDSC = 'Outpatient'
          --Only DFCI
          AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
          AND HT.TransactionTypeDSC = 'Charge'

  ) UN

WHERE UN.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')



GROUP BY 

CASE WHEN Month(UN.DischargeDT) > 9 THEN YEAR(UN.DischargeDT) + 1 ELSE YEAR(UN.DischargeDT) END
,UN.MRN

) D
) FLG

ON FLG.MRN = CH.MRN AND FLG.FY = CH.FY


LEFT JOIN
(
--New Patient Date
SELECT 
NP.MRN
,MIN(NP.DischargeDT) AS New_Patient_Date

FROM(

        SELECT 
        CAST(EE.DischargeDTS AS DATE) AS DischargeDT
        ,EC.HCPCS AS HCPCS_CPT
        ,CAST(EC.UserField16NBR AS INT) AS Units
        ,EE.MRN
        ,EC.DetailTotalChargeAMT

          FROM [EPSI].Cost.OutpatientCharge EC
          LEFT JOIN [EPSI].Cost.PatientEncounter EE ON EC.PatientAccountID = EE.PatientAccountID
          --EPSI FY15 and before
          WHERE CASE WHEN Month(EE.DischargeDTS) > 9 THEN YEAR(EE.DischargeDTS) + 1 ELSE YEAR(EE.DischargeDTS) END <=2015

        UNION ALL

        --EPIC
        SELECT 
        CAST(HA.DischargeDTS AS DATE) AS DischargeDT
        ,CASE WHEN LEN(HT.CPT) <> 5 THEN HT.HCPCS ELSE HT.CPT END AS HCPCS_CPT
        ,CASE WHEN HT.ChargeModifierListTXT LIKE '%DFP%' THEN 0 ELSE CAST(HT.TransactionCNT AS INT) END AS Units
        ,I.PatientIdentityID AS MRN
        ,HT.Transact
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-09-09T02:41:28.283+00:00

    Hi @wheddingsjr ,

    As mentioned in the error message, you need to have an equal number of expressions
    using UNION or UNION ALL.

    You need to add one column like in below red box by either join EPIC.Reference.[Location] table or leave it as null or ''.
    23220-untitled.png

    For example:

    You will get the error 'All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists' after ran below query: 

    SELECT 1 Col1 , 2 Co2  
UNION  
SELECT 5

    The correct one should be as below: 

    SELECT 1 Col1 , 2 Co2  
UNION  
SELECT 5,NULL

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. wheddingsjr 1 Reputation point
    2020-09-09T17:37:28.863+00:00

    Thanks Melissa, I added Loc.RevenueLocationNM on all of the unions using Epic. So I have to add '' on all of the EPSI codes? I didn't add anything to those.

    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2020-09-10T00:59:26.957+00:00

    Hi @wheddingsjr ，

    According to your query posted, you only added one column 'Loc.RevenueLocationNM' in below part. So you could only add one column as below and have another try.

    23643-nm.png
    If you still face this error, please provide the lastest query if possible and DDL&Sample data could be better.

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  4. wheddingsjr 1 Reputation point
    2020-09-10T14:26:28.9+00:00

    Hi again @MelissaMa-MSFT

    I used the Null RevenueLocationNM as well as the '' RevenueLocationNM and though it did create the column, the results in the column is either blank or "NULL" which cant be possible as every line should have a location name. I tried just creating a temp table and adding the RevenueLocationNM to that but out of 50M lines, only 25M ends up having a location name. Unfortunately I cannot include any data as it is HIPPA protected data.

    Thanks

    William

    0 comments
  5. Guoxiong 8,126 Reputation points
    2020-09-10T18:49:20.83+00:00

    Based on your code, the column RevenueLocationNM is in the table [EPIC].[Reference].[Location]. You need to find out the relationship between the table [EPIC].[Reference].[Location] and the table [EPSI].[Cost].[OutpatientCharge] or [EPSI].[Cost].[PatientEncounter]. And then you can add the column RevenueLocationNM (instead of NULL) to the SELECT statement using LEFT JOIN.

    0 comments