I don't see any missing indexes on the execution plan apart from Hash Match, Parallelism and Index Scan.
I get 218,673 rows in 5 minutes. Planning the improve the performance. Here is the sql query, let me know if there is room to improvement:
SELECT epi.EPI_ID as EPI_ID ,ISNULL(npi.ProviderLastName,'') + ', ' + ISNULL(npi.ProviderDegree,'') + ' ' + ISNULL(npi.ProviderFirstName,'') as ProviderLastNameFirst ,ISNULL(npi.ProviderFirstName,'') + ' ' + ISNULL(npi.ProviderLastName,'') + ' ' + ISNULL(npi.ProviderDegree,'') + ' (' + CONVERT(varchar(20),e.ProviderNPI) + ')' as 'EPI Top Rank Provider' ,CASE WHEN e.PracticeProviderAffiliatedFlag = 1 THEN ISNULL(npi.ProviderFirstName,'') + ' ' + ISNULL(npi.ProviderLastName,'') + ' ' + ISNULL(npi.ProviderDegree,'') + ' (' + CONVERT(varchar(20),e.ProviderNPI) + ')' ELSE NULL END as 'EPI Top Rank Provider w Affiliation' ,epi.DRG_CD + ' - ' + drg.[MS_DRG_Title] as DRG ,epi.ANCHOR_BEG_DT as 'Anchor Start Date' ,cpl.CarePlanName as 'EPI Joint Type' ,e.CPT + ' ' + cpt.[Description] as 'CPT with Description' ,CASE WHEN e.Trauma = 1 THEN 'Trauma' ELSE 'Elective' END as 'Surgery Type' ,ccn.LocationName + ' (' + e.PROVIDER_CCN +')' as 'Acute Provider' ,LEFT(DATENAME(month,epi.ANCHOR_BEG_DT),3) as 'Month' ,YEAR(epi.ANCHOR_BEG_DT) as 'Year' ,p.LastName + ', ' + p.FirstName + CASE WHEN p.MiddleName IS NULL THEN '' ELSE ' ' + LEFT(p.MiddleName,1) END as 'Patient Name' ,p.DateOfBirth as 'Patient DOB' ,CASE WHEN p.AGE < 65 THEN '< 65' WHEN p.AGE BETWEEN 65 AND 70 THEN '65-70' WHEN p.AGE BETWEEN 71 AND 75 THEN '71-75' WHEN p.AGE BETWEEN 76 AND 80 THEN '76-80' WHEN p.AGE BETWEEN 81 AND 85 THEN '81-85' WHEN p.AGE IS NULL THEN NULL ELSE '86+' END as 'Age Range' ,1 as 'EPIs' ,epi.EPI_ALLOWED_PAY as 'Total Spend' ,ext.AcuteTotalSpend as 'Acute Total Spend' ,ext.AcutePartATotalSpend as 'Acute Part A Total Spend' ,ext.AcutePartBTotalSpend as 'Acute Part B Total Spend' ,ext.PostAcuteTotalSpend - ISNULL(ext.ReadmitTotalSpend,0.00) as 'Post Acute Total Spend' ,case when ext.IRFTotalSpend > 0 THEN 1 else 0 END as 'IRF EPIs' ,case when ext.IRFTotalSpend > 0 THEN ext.IRFTotalSpend ELSE NULL END as 'IRF Total Spend' ,irf.LengthOfStay as 'IRF Total Days' ,case when ext.SNFTotalSpend > 0 OR ISNULL(snf.LengthOfStay,0) > 0 THEN 1 else 0 END as 'SNF EPIs' ,case when ext.SNFTotalSpend > 0 THEN ext.SNFTotalSpend ELSE NULL END as 'SNF Total Spend' ,snf.LengthOfStay as 'SNF Total Days' ,case when ext.HHTotalSpend > 0 OR ISNULL(hhh.VisitCount,0) > 0 THEN 1 else 0 END as 'HH EPIs' ,case when ext.HHTotalSpend > 0 THEN ext.HHTotalSpend ELSE NULL END as 'HH Total Spend' ,hhh.LengthOfStay as 'HH Total Days' ,ext.OutpatientTotalSpend as 'Outpatient Total Spend' ,CASE WHEN ext.ReadmitTotalSpend > 0 THEN ext.ReadmitTotalSpend ELSE NULL END as 'Readmit Total Spend' ,CASE WHEN ISNULL(r.[Readmit Incidents],0) > 0 THEN 1 ELSE 0 END AS 'Readmit EPIs' ,ISNULL(r.[Readmit Incidents],0) as 'Readmit Incidents' ,ext.PostAcutePartBTotalSpend as 'Readmit & Post Acute Part B Total Spend' --,case when ext.ReadmitTotalSpend > 0 THEN ext.ReadmitTotalSpend+ext.PostAcutePartBTotalSpend ELSE NULL END as 'Readmit & Post Acute Part B Total Spend' ,ext.DMETotalSpend as 'DME Total Spend' ,ext.LTCHTotalSpend as 'LTCH Total Spend' ,CASE WHEN e.PracticeProviderAffiliatedFlag = 1 THEN 'Affiliated' ELSE 'Not Affiliated' END as 'Affiliation' ,epi.BPID as 'Group ID' ,ent.[Name] as 'Group' ,ent.[Name] as 'Selected Group' ,CASE WHEN e.DRGAssigned = 1 THEN 'TRUE' ELSE 'FALSE' END as 'Include/Exclude?' ,CONVERT(varchar(20),e.ProviderNPI) as 'ProviderNPI' ,substring(datename(mm, getdate()),1,3) + cast(datepart(yyyy, getdate()) as varchar) as [Date] ,'' as [Period] ,'' as TimeFrame ,hhh.VisitCount as 'HH Visit Count' ,ext.WindsorizedTotalSpend AS 'Total Spend (Winsorized)' ,ext.NPRA as 'NPRA' ,CASE WHEN ISNULL(oph.Visits,0) > 0 THEN 1 ELSE 0 END as 'OP PT EPIs' ,oph.Visits as 'OP PT Visit Count' ,oph.REVPMT+oph.PTNRSP as 'OP PT Total Spend' ,pp.PracticeID ,isnull(ppp.Name, 'Unknown [' + cast(isnull(pp.PracticeID,0) as varchar)+ ']') as 'Practice Name' ,epi.EpisodeID ,e.EnterpriseID ,e.DRG_CD as DRG_Code ,e.TargetTrauma ,e.CJRTrauma ,e.DRGAssigned ,ent.Name as EnterpriseName FROM dbo.[EPI] epi LEFT JOIN dbo.[EPI_EXT] ext ON epi.EPIRecordID = ext.EPIRecordID LEFT JOIN dbo.[Episode] e ON epi.EpisodeID = e.EpisodeID LEFT JOIN dbo.[Patient] p ON e.PatientID = p.PatientID LEFT JOIN dbo.[LU_MS_DRG_VALUES] drg ON epi.DRG_CD = drg.MS_DRG AND YEAR(epi.ANCHOR_END_DT) = drg.[Year] LEFT JOIN dbo.[MasterNPI] npi ON e.ProviderNPI = npi.NPI LEFT JOIN dbo.[PracticeProvider] pp ON e.ProviderNPI = pp.ProviderNPI AND e.BPID = pp.BPID AND e.ANCHOR_BEG_DT >= pp.BPCIBeginDate AND e.ANCHOR_BEG_DT < ISNULL(pp.BPCIEndDate,CONVERT(date,'2099-12-31')) LEFT JOIN dbo.[Practice] ppp ON pp.PracticeID = ppp.PracticeID LEFT JOIN dbo.[MasterCCN] ccn ON ccn.CCN = e.PROVIDER_CCN LEFT JOIN dbo.[Enterprises] ent ON e.EnterpriseID = ent.EnterpriseID LEFT JOIN dbo.[MasterCPT] cpt ON e.CPT = cpt.CPT LEFT JOIN dbo.[MasterCarePlans] cpl ON cpt.DefaultPinPointCarePlanID = cpl.PinPointCarePlanID LEFT JOIN ( SELECT ip.EpisodeID, COUNT(*) as 'Readmit Incidents' FROM dbo.[IPHDR] ip LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID WHERE ipx.AnchorStay = 0 AND ipx.ProviderType NOT IN ('IRF','LTCH') AND ip.COSTINC = 1 GROUP BY ip.EpisodeID ) r ON epi.EpisodeID = r.EpisodeID LEFT JOIN ( SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay FROM dbo.[IPHDR] ip LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID WHERE ipx.ProviderType IN ('IRF') GROUP BY ip.EpisodeID ) irf ON epi.EpisodeID = irf.EpisodeID LEFT JOIN ( SELECT sn.EpisodeID, SUM(snx.LengthOfStay) as LengthOfStay FROM dbo.[SNHDR] sn LEFT JOIN dbo.[SNHDR_EXT] snx ON sn.SNHDRRecordID = snx.SNHDRRecordID GROUP BY sn.EpisodeID ) snf ON epi.EpisodeID = snf.EpisodeID LEFT JOIN ( SELECT hh.EpisodeID, SUM(hhx.LengthOfStay) as LengthOfStay, (SELECT COUNT(DISTINCT REV_DT) FROM dbo.[HHDTL] WHERE EpisodeID = hh.EpisodeID) AS VisitCount FROM dbo.[HHHDR] hh LEFT JOIN dbo.[HHHDR_EXT] hhx ON hh.HHHDRRecordID = hhx.HHHDRRecordID GROUP BY hh.EpisodeID ) hhh ON epi.EpisodeID = hhh.EpisodeID LEFT JOIN ( SELECT dtl.EpisodeID ,COUNT(DISTINCT [REV_DT]) AS Visits ,SUM([REVPMT]) as REVPMT ,SUM([PTNRSP]) as PTNRSP FROM dbo.[OPDTL] dtl where REV_CNTR in ('0420','0421','0422','0423','0424','0429','0977') GROUP BY dtl.EpisodeID ) oph ON epi.EpisodeID = oph.EpisodeID