Simplifying a query

Transact-SQL
1

Hello all

I am in need of some help. I inherited code that was nested and the run time is approx. 7 mins. However, when i use the code in Tableau it takes approx. 25 mins. to run. I have been told that the code could be simpler and would run quicker in SQL and more importantly in Tableau if i un-nested the code. Unfortunately, I have been trying all day to do it but I am having a hard time. Any assistance would be greatly appreciated. Below is the code.

	CH.FY, CH.MRN, RevenueLocationNM, ActivityType, HCPCS_CPT, DischargeDT
	, CASE WHEN NEW.MRN IS NOT NULL THEN 'New'
	  ELSE 'Established'
	  END AS FY_New_Established
	--, NEW.New_Patient_Date

FROM
		(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
		,Loc.RevenueLocationNM
	
		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
			/*Activity Type*/
			(SELECT DISTINCT 
					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

			FROM
				(SELECT
				       UN.MRN, CASE WHEN Month(UN.DischargeDT) > 9 THEN YEAR(UN.DischargeDT) + 1 ELSE YEAR(UN.DischargeDT) END AS FY
					   ,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')
						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"
				FROM
					(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
						   ,Loc.RevenueLocationNM
				
					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' /*ym10092020*/
						  /* Only DFCI */
						  AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
						  AND HT.TransactionTypeDSC = 'Charge' /*ym10092020*/

					) UN
					WHERE UN.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
					GROUP BY 
							UN.MRN, CASE WHEN Month(UN.DischargeDT) > 9 THEN YEAR(UN.DischargeDT) + 1 ELSE YEAR(UN.DischargeDT) END
				) D
			) FLG ON FLG.MRN = CH.MRN AND FLG.FY = CH.FY


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

		FROM(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
				,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 1=1
				  AND 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.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```
2

Can you provide DDL and sample data? Or at least the execution plan. The query doesn't help us with where the issue is

3

There are quite a few issues here - so lets start with one of the derived tables and see if we can get that to work better.

       (SELECT DISTINCT
			   NP.MRN
			  ,MIN(NP.DischargeDT) AS New_Patient_Date

		FROM(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
				,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 1=1
				  AND 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.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

First thing I notice is that you are calculating whether or not you want to look at the HCPCS or CPT code which isn't needed. You can adjust the WHERE clause for that to:

WHERE ...
  AND (
       HT.CPT IN ({your list of valid CPT code})
   OR HT.HCPCS IN ({your list of valid HCPCS codes})
  AND ...

Then - I see you calculating the Units...but you can avoid that calculation and use it as part of the WHERE:

WHERE ...
  AND (
       HT.CPT IN ({your list of valid CPT code})
   OR HT.HCPCS IN ({your list of valid HCPCS codes})
  AND HT.ChargeModifierListTXT NOT LIKE '%DFP%'

And - if there are still TransactionCNT values equal to 0 that need to be excluded:

WHERE ...
  AND (
       HT.CPT IN ({your list of valid CPT code})
   OR HT.HCPCS IN ({your list of valid HCPCS codes})
  AND HT.ChargeModifierListTXT NOT LIKE '%DFP%'
  AND HT.TransactionCNT > 0

Note: HT.TransactionCNT should already be a numeric value, if it is stored as a string then you need to go back to whoever created the table and find out why they are setting a CNT column as a string instead of a valid numeric data type.

The next problem is the date filtering - which cannot utilize an index because of the functions and the CASE expression. In the inner query, I think you can just use:

  AND HA.DischargeDTS >= '2015-10-01'

That would include all discharges starting in FY 2016.

I would start with this...

(SELECT I.PatientIdentityID AS MRN
      , MIN(CAST(HA.DischargeDTS AS DATE)) AS New_Patient_Date
   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 HA.DischargeDTS) >= '2015-10-01'
        /* Only Outpatient */
    AND HT.HospitalAccountClassDSC = 'Outpatient'
        /* Only DFCI */
    AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
    AND HT.TransactionTypeDSC = 'Charge'
    AND (
        HP.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')
     OR HP.HCPCS 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 HT.TransactionCNT > 0
    AND HT.ChargeModifierListTXT NOT LIKE '%DFP%'
  GROUP BY 
        I.PatientIdentityID
        )                                       AS 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

Now - the relationship between CH and this derived table appears to be on the FY even though both are pulling from the same table. Basically - if looks like you are relating the earliest discharge date to all discharges in the same fiscal year and that just doesn't seem right to me...

That would be the next step - I think. Figure out how to relate these items without calculating the fiscal year since it should not be needed. It looks like every query is excluding any discharges prior to the start of FY 2016.

You also have everything set to a LEFT JOIN but then are filtering on those tables, essentially making them perform as an INNER JOIN. Change them to an INNER JOIN where you can.

Once you have simplified one - test and validate the results are the same, then tackle the next one.

4

Thanks Jeff..I will give this a try...however, the odd thing is (I am new to the company and have never seen anything like this), the CPT and HCPCS for some strange reason cannot be separated based on the way its set up. Does that matter?

5

Not quite sure on your setup - the reality is that CPT codes are a subset of HCPCS codes. The numeric codes are CPT (Level I) and the non-numeric are HCPCS (Level II). They are used for different items and services.

So if you were to look in a lookup table of all available HCPCS codes - that list would (should) include all available CPT codes. With that said - filtering on the codes being in either is valid although you may not find any CPT codes in the HCPCS column.

6

One more thing - I was not looking at the specifics...the idea here is to look at how the code is written and how we can reduce the level of nesting.

When looking at a nested query - first we need to determine why it was nested and if we can un-nest it and get the same result. In this case, the nested query can be un-nested by eliminating the extra columns and calculations and performing the grouping on the nested query.

One final note - there is no reason to have both DISTINCT and GROUP BY in the same query. I though I had eliminated that...but there was a bad copy/paste on my part. I have edited and fixed that.

1 Like