Hi all
I am trying to add a field to an inherited code that has multiple unions. I added the additional field to what I thought was all the places it needed to be added but I still continue to get the 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 field I am trying to add is: "Loc.RevenueLocationNM"
This is the code:
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.TransactionAMT AS DetailTotalChargeAMT
,Loc.RevenueLocationNM
FROM [Epic].[Finance].[HospitalTransaction] HT
LEFT JOIN [Epic].[Finance].[HospitalAccount] HA ON HA.HospitalAccountID = HT.HospitalAccountID
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'
) NP
WHERE
NP.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 NP.DetailTotalChargeAMT <> 0
AND NP.Units > 0
GROUP BY NP.MRN
) NEW ON CH.MRN = NEW.MRN AND CH.FY = CASE WHEN Month(NEW.New_Patient_Date) > 9 THEN YEAR(NEW.New_Patient_Date) + 1 ELSE YEAR(NEW.New_Patient_Date) END
