Health Care "Bundling" - A Challenge

CC_DA · May 13, 2016, 6:41pm

Hi All,

I am attempting to create a process to identify the earliest occurrence of chemo by patient and attribute all related activity for the next 180 days to that "anchor" chemo treatment. At that point, I would like to also identify the next chemo treatment (after the initial 180 days) as another episode...with the same idea - 180 days of activity attributed to the new anchor. There is no limit to the number of episodes a patient can have.

I have been able to identify the earliest chemo treat by patient but I am having difficulty identifying the start of the new episodes (the earliest chemo treat after the first 180 days). I am also struggling with how to attribute all activity to the anchor chemo treat(s).

Any help or direction as to the best approach would be very much appreciated. I would want the output to be something like the following...where based on EMPI / ENC# / Date it identifies the anchor

***Assuming these are all Chemo Treats below (sometimes there will be other activity in the mix)

EMPI MRN ENC# Date Anchor ID
00001 123 1000 1/1/2014 Yes 1
00001 123 1001 3/1/2014 No 1
00001 123 1002 4/1/2014 No 1
00001 123 1003 9/1/2014 Yes 2
00001 123 1004 10/1/2014 No 2

gbritton · May 13, 2016, 8:06pm

do the 180-day periods start on the quarters? Jan 1, Apr 1, Jul 1, Oct 1?

Also, now long max will a patient be in chemo? 1 year, 2 years, 7 years, 100 years?

bitsmed · May 13, 2016, 9:45pm

This is one way of doing it:

with cte1
  as (select empi
            ,mrn
            ,min([date]) as [date]
            ,cast(1 as bigint) as rn
        from yourtable
       group by empi
               ,mrn
      union all
      select a.empi
            ,a.mrn
            ,b.[date]
            ,row_number() over(order by b.[date]) as rn
        from cte1 as a
             inner join yourtable as b
                     on b.empi=a.empi
                    and b.mrn=a.mrn
                    and b.[date]>dateadd(day,180,a.[date])
       where a.rn=1
     )
    ,cte2
  as (select empi
            ,mrn
            ,[date]
            ,row_number() over(partition by empi
                                           ,mrn
                               order by [date]
                              )
             as rn
        from cte1
       where rn=1
     )
select b.empi
      ,b.mrn
      ,b.[enc#]
      ,b.[date]
      ,case
          when a.[date]=b.[date]
          then 'Yes'
          else 'No'
       end as anchor
      ,a.rn as id
  from cte2 as a
       inner join yourtable as b
               on b.empi=a.empi
              and b.mrn=a.mrn
              and b.[date]>=a.[date]
              and b.[date]<dateadd(day,180,a.[date])
 order by b.empi
         ,b.mrn
         ,b.[date]
;

stephen_hendricks · May 13, 2016, 10:11pm

Sweet! I was trying to put a recursive CTE together myself but I couldn't figure out how to get the MIN date that was greater than 180 days from the previous "anchor" date. The ROW_NUMBER() you've used does that nicely.

CC_DA · May 16, 2016, 12:19am

The 180 days begin when the patient first starts chemo...can be any date. Then the earliest chemo treatment after the first 180 days (episodic treatment) begins a new episode. There is really no max treatment time although I doubt that a patient will be in chemo for 100 years... ;).

CC_DA · May 16, 2016, 12:31am

Wow, impressive. From the research I had done, I knew it was going to be some form of a recursive CTE. I am fairly new to that level of SQL writing though. I am going to try it out tomorrow. This sort of bundling is usually done with a module in EPSi.

CC_DA · May 16, 2016, 2:47pm

Just curious (because I don't know), would you expect the run time on this to be greater than other solutions? Do recursive CTEs take a while to run?

Here is what the final query ended up being...I had to add an additional CTE to get the data how I wanted it...

It is still running as I type at about 1 Hour 30 min. Perhaps I did something wrong adding the other CTE.

with cte0 as (SELECT ddw.Patient.EMPI
, ddw.Patient.Medical_Record_Number
, dbo.Encounter.Encounter_Number
, fixed.Date.Date
FROM ddw.Diagnosis RIGHT OUTER JOIN
dbo.Encounter_Diagnosis ON ddw.Diagnosis.Diagnosis_Key = dbo.Encounter_Diagnosis.Diagnosis_Key RIGHT OUTER JOIN
dbo.Charge LEFT OUTER JOIN
ddw.HCPCS ON dbo.Charge.HCPCS_Key = ddw.HCPCS.HCPCS_Key RIGHT OUTER JOIN
dbo.Encounter ON dbo.Charge.Encounter_Key = dbo.Encounter.Encounter_Key LEFT OUTER JOIN
ddw.CCF_Payor ON dbo.Encounter.CCF_Payor_Key = ddw.CCF_Payor.CCF_Payor_Key LEFT OUTER JOIN
ddw.SL_Physician ON dbo.Encounter.Service_Line_Physician_Key = ddw.SL_Physician.Physician_Key LEFT OUTER JOIN
ddw.Patient ON dbo.Encounter.Patient_Key = ddw.Patient.Patient_Key LEFT OUTER JOIN
fixed.Date ON dbo.Encounter.Discharge_Date_Key = fixed.Date.Date_Key ON
dbo.Encounter_Diagnosis.Encounter_Key = dbo.Encounter.Encounter_Key
WHERE (fixed.Date.Year > 2014) AND (ddw.CCF_Payor.CCF_Payor_Code IN ('1', '2')) AND (ddw.SL_Physician.Service_Line_Institute_Code = 'CAN00') AND
(ddw.HCPCS.HCPCS_Code IN ('96401', '96402', '96409', '96411', '96413', '96415', '96416', '96417', '96446', '96450', '96521', '96522', '96523', '96542', '96549')) AND
(ddw.Diagnosis.Diagnosis_Code LIKE 'C910%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C913%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C915%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C916%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C91a%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C920%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C923%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C924%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C925%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C926%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C92a%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C930%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C940%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C942%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C943%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C950%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C21%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C65%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C66%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C67%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C68%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C50%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C911%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C921%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C70%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C71%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C72%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C73%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C74%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C75%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C7A%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C51%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C52%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C53%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C54%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C55%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C15%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C16%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C00%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C01%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C02%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C03%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C04%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C05%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C06%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C07%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C08%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C09%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C10%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C11%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C12%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C13%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C14%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C30%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C31%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C32%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C33%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C69%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C760%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C17%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C18%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C19%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C20%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C64%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C22%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C23%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C24%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C34%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C39%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C45%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C81%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C82%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C83%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C84%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C85%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C86%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C88%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C914%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C43%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C946%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'D46%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C90%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C56%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C25%' OR
ddw.Diagnosis.Diagnosis_Code LIKE 'C61%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2040%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2053%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2060%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2070%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2072%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2080%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1542%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1543%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1548%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '188%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1891%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1892%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1893%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1894%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1898%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1899%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '174%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '175%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2041%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2051%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '191%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1920%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1921%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1922%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1923%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1928%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1929%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '193%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1940%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1941%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1943%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1944%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1945%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1946%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1948%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1949%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2090%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2091%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2092%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '179%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '180%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '182%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1840%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1841%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1842%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1843%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1844%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '150%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '151%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '140%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1410%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1411%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1412%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1413%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1414%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1415%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1416%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1418%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1419%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1420%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1421%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1422%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1428%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1429%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '143%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '144%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1450%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1451%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1452%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1453%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1454%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1455%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1456%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1458%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1459%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1460%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1461%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1462%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1463%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1464%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1465%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1466%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1467%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1468%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1469%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '147%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1480%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1481%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1482%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1483%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1488%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1489%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '149%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1600%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1601%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1602%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1603%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1604%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1605%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1608%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1609%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '161%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1620%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '190%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1950%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '152%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '153%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1540%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1541%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1890%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '155%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1560%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1561%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1562%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1568%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1569%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1622%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1623%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1624%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1625%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1628%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1629%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '165%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2000%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2001%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2002%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2003%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2004%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2005%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2006%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2007%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2008%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '201%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2020%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2021%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2022%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2024%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2027%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2733%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '172%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2030%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '2031%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '1830%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '157%' OR
ddw.Diagnosis.Diagnosis_Code LIKE '185%' OR
ddw.Diagnosis.Diagnosis_Code = '20500' OR
ddw.Diagnosis.Diagnosis_Code = '20501' OR
ddw.Diagnosis.Diagnosis_Code = '20502' OR
ddw.Diagnosis.Diagnosis_Code = '20930' OR
ddw.Diagnosis.Diagnosis_Code = '20280' OR
ddw.Diagnosis.Diagnosis_Code = '20281' OR
ddw.Diagnosis.Diagnosis_Code = '20282' OR
ddw.Diagnosis.Diagnosis_Code = '20283' OR
ddw.Diagnosis.Diagnosis_Code = '20284' OR
ddw.Diagnosis.Diagnosis_Code = '20285' OR
ddw.Diagnosis.Diagnosis_Code = '20286' OR
ddw.Diagnosis.Diagnosis_Code = '20287' OR
ddw.Diagnosis.Diagnosis_Code = '20288' OR
ddw.Diagnosis.Diagnosis_Code = '20380' OR
ddw.Diagnosis.Diagnosis_Code = '20382' OR
ddw.Diagnosis.Diagnosis_Code = '20381' OR
ddw.Diagnosis.Diagnosis_Code = '23872' OR
ddw.Diagnosis.Diagnosis_Code = '23873' OR
ddw.Diagnosis.Diagnosis_Code = '23874' OR
ddw.Diagnosis.Diagnosis_Code = '23875')
GROUP BY ddw.Patient.EMPI, ddw.Patient.Medical_Record_Number, dbo.Encounter.Encounter_Number, fixed.Date.Date) ,

cte1
as (select cte0.EMPI
,cte0.Medical_Record_Number
,min(cte0.[date]) as [date]
,cast(1 as bigint) as rn
from cte0
group by EMPI
,Medical_Record_Number
union all
select a.EMPI
,a.Medical_Record_Number
,b.[date]
,row_number() over(order by b.[date]) as rn
from cte1 as a
inner join cte0 AS b
on b.EMPI=a.EMPI
and b.Medical_Record_Number=a.Medical_Record_Number
and b.[date]>dateadd(day,180,a.[date])
where a.rn=1
)
,cte2
as (select empi
,Medical_Record_Number
,[date]
,row_number() over(partition by EMPI
,Medical_Record_Number
order by [date]
)
as rn
from cte1
where rn=1
)
select b.EMPI
,b.Medical_Record_Number
,b.Encounter_Number
,b.[date]
,case
when a.[date]=b.[date]
then 'Yes'
else 'No'
end as 'anchor'
,a.rn as id
from cte2 as a
inner join cte0 AS b
on b.empi=a.empi
and b.Medical_Record_Number=a.Medical_Record_Number
and b.[date]>=a.[date]
and b.[date]<dateadd(day,180,a.[date])
order by b.empi
,b.Medical_Record_Number
,b.[date]
;

gbritton · May 16, 2016, 2:51pm

Recursive CTEs can be bad performers.

bitsmed · May 16, 2016, 7:49pm

Your joins seems messed up. I have tried tidying up the query (following sql logic), so I have changed most joins to inner joins. I don't like right joins, so if possible, see if it is possible to change it to inner or left join.
Anyway, here goes:

with cte0
  as (select pat.EMPI
            ,pat.Medical_Record_Number
            ,e.Encounter_Number
            ,d.[Date]
        from ddw.Diagnosis as d
             inner join dbo.Encounter_Diagnosis as ed
                     on ed.Diagnosis_Key=d.Diagnosis_Key
             right outer join dbo.Charge as c
                           on c.Encounter_Key=ed.Encounter_Key
             inner join ddw.HCPCS as h
                     on h.HCPCS_Key=c.HCPCS_Key
                    and h.HCPCS_Code in ('96401'
                                        ,'96402'
                                        ,'96409'
                                        ,'96411'
                                        ,'96413'
                                        ,'96415'
                                        ,'96416'
                                        ,'96417'
                                        ,'96446'
                                        ,'96450'
                                        ,'96521'
                                        ,'96522'
                                        ,'96523'
                                        ,'96542'
                                        ,'96549'
                                        )
             inner join dbo.Encounter as e
                     on e.Encounter_Key=c.Encounter_Key
             inner join ddw.CCF_Payor as pay
                     on pay.CCF_Payor_Key=e.CCF_Payor_Key
                    and pay.CCF_Payor_Code in ('1','2')
             inner join ddw.SL_Physician as phy
                     on phy.Physician_Key=e.Service_Line_Physician_Key
                    and phy.Service_Line_Institute_Code='CAN00'
             left outer join ddw.Patient as pat
                          on pat.Patient_Key=e.Patient_Key
             inner join fixed.[Date] as d
                     on d.Date_Key=e.Discharge_Date_Key
                    and d.Year>2014
       where d.Diagnosis_Code like 'C910%'
          or d.Diagnosis_Code like 'C913%'
          or d.Diagnosis_Code like 'C915%'
          or d.Diagnosis_Code like 'C916%'
          or d.Diagnosis_Code like 'C91a%'
          or d.Diagnosis_Code like 'C920%'
          or d.Diagnosis_Code like 'C923%'
          or d.Diagnosis_Code like 'C924%'
          or d.Diagnosis_Code like 'C925%'
          or d.Diagnosis_Code like 'C926%'
          or d.Diagnosis_Code like 'C92a%'
          or d.Diagnosis_Code like 'C930%'
          or d.Diagnosis_Code like 'C940%'
          or d.Diagnosis_Code like 'C942%'
          or d.Diagnosis_Code like 'C943%'
          or d.Diagnosis_Code like 'C950%'
          or d.Diagnosis_Code like 'C21%'
          or d.Diagnosis_Code like 'C65%'
          or d.Diagnosis_Code like 'C66%'
          or d.Diagnosis_Code like 'C67%'
          or d.Diagnosis_Code like 'C68%'
          or d.Diagnosis_Code like 'C50%'
          or d.Diagnosis_Code like 'C911%'
          or d.Diagnosis_Code like 'C921%'
          or d.Diagnosis_Code like 'C70%'
          or d.Diagnosis_Code like 'C71%'
          or d.Diagnosis_Code like 'C72%'
          or d.Diagnosis_Code like 'C73%'
          or d.Diagnosis_Code like 'C74%'
          or d.Diagnosis_Code like 'C75%'
          or d.Diagnosis_Code like 'C7A%'
          or d.Diagnosis_Code like 'C51%'
          or d.Diagnosis_Code like 'C52%'
          or d.Diagnosis_Code like 'C53%'
          or d.Diagnosis_Code like 'C54%'
          or d.Diagnosis_Code like 'C55%'
          or d.Diagnosis_Code like 'C15%'
          or d.Diagnosis_Code like 'C16%'
          or d.Diagnosis_Code like 'C00%'
          or d.Diagnosis_Code like 'C01%'
          or d.Diagnosis_Code like 'C02%'
          or d.Diagnosis_Code like 'C03%'
          or d.Diagnosis_Code like 'C04%'
          or d.Diagnosis_Code like 'C05%'
          or d.Diagnosis_Code like 'C06%'
          or d.Diagnosis_Code like 'C07%'
          or d.Diagnosis_Code like 'C08%'
          or d.Diagnosis_Code like 'C09%'
          or d.Diagnosis_Code like 'C10%'
          or d.Diagnosis_Code like 'C11%'
          or d.Diagnosis_Code like 'C12%'
          or d.Diagnosis_Code like 'C13%'
          or d.Diagnosis_Code like 'C14%'
          or d.Diagnosis_Code like 'C30%'
          or d.Diagnosis_Code like 'C31%'
          or d.Diagnosis_Code like 'C32%'
          or d.Diagnosis_Code like 'C33%'
          or d.Diagnosis_Code like 'C69%'
          or d.Diagnosis_Code like 'C760%'
          or d.Diagnosis_Code like 'C17%'
          or d.Diagnosis_Code like 'C18%'
          or d.Diagnosis_Code like 'C19%'
          or d.Diagnosis_Code like 'C20%'
          or d.Diagnosis_Code like 'C64%'
          or d.Diagnosis_Code like 'C22%'
          or d.Diagnosis_Code like 'C23%'
          or d.Diagnosis_Code like 'C24%'
          or d.Diagnosis_Code like 'C34%'
          or d.Diagnosis_Code like 'C39%'
          or d.Diagnosis_Code like 'C45%'
          or d.Diagnosis_Code like 'C81%'
          or d.Diagnosis_Code like 'C82%'
          or d.Diagnosis_Code like 'C83%'
          or d.Diagnosis_Code like 'C84%'
          or d.Diagnosis_Code like 'C85%'
          or d.Diagnosis_Code like 'C86%'
          or d.Diagnosis_Code like 'C88%'
          or d.Diagnosis_Code like 'C914%'
          or d.Diagnosis_Code like 'C43%'
          or d.Diagnosis_Code like 'C946%'
          or d.Diagnosis_Code like 'D46%'
          or d.Diagnosis_Code like 'C90%'
          or d.Diagnosis_Code like 'C56%'
          or d.Diagnosis_Code like 'C25%'
          or d.Diagnosis_Code like 'C61%'
          or d.Diagnosis_Code like '2040%'
          or d.Diagnosis_Code like '2053%'
          or d.Diagnosis_Code like '2060%'
          or d.Diagnosis_Code like '2070%'
          or d.Diagnosis_Code like '2072%'
          or d.Diagnosis_Code like '2080%'
          or d.Diagnosis_Code like '1542%'
          or d.Diagnosis_Code like '1543%'
          or d.Diagnosis_Code like '1548%'
          or d.Diagnosis_Code like '188%'
          or d.Diagnosis_Code like '1891%'
          or d.Diagnosis_Code like '1892%'
          or d.Diagnosis_Code like '1893%'
          or d.Diagnosis_Code like '1894%'
          or d.Diagnosis_Code like '1898%'
          or d.Diagnosis_Code like '1899%'
          or d.Diagnosis_Code like '174%'
          or d.Diagnosis_Code like '175%'
          or d.Diagnosis_Code like '2041%'
          or d.Diagnosis_Code like '2051%'
          or d.Diagnosis_Code like '191%'
          or d.Diagnosis_Code like '1920%'
          or d.Diagnosis_Code like '1921%'
          or d.Diagnosis_Code like '1922%'
          or d.Diagnosis_Code like '1923%'
          or d.Diagnosis_Code like '1928%'
          or d.Diagnosis_Code like '1929%'
          or d.Diagnosis_Code like '193%'
          or d.Diagnosis_Code like '1940%'
          or d.Diagnosis_Code like '1941%'
          or d.Diagnosis_Code like '1943%'
          or d.Diagnosis_Code like '1944%'
          or d.Diagnosis_Code like '1945%'
          or d.Diagnosis_Code like '1946%'
          or d.Diagnosis_Code like '1948%'
          or d.Diagnosis_Code like '1949%'
          or d.Diagnosis_Code like '2090%'
          or d.Diagnosis_Code like '2091%'
          or d.Diagnosis_Code like '2092%'
          or d.Diagnosis_Code like '179%'
          or d.Diagnosis_Code like '180%'
          or d.Diagnosis_Code like '182%'
          or d.Diagnosis_Code like '1840%'
          or d.Diagnosis_Code like '1841%'
          or d.Diagnosis_Code like '1842%'
          or d.Diagnosis_Code like '1843%'
          or d.Diagnosis_Code like '1844%'
          or d.Diagnosis_Code like '150%'
          or d.Diagnosis_Code like '151%'
          or d.Diagnosis_Code like '140%'
          or d.Diagnosis_Code like '1410%'
          or d.Diagnosis_Code like '1411%'
          or d.Diagnosis_Code like '1412%'
          or d.Diagnosis_Code like '1413%'
          or d.Diagnosis_Code like '1414%'
          or d.Diagnosis_Code like '1415%'
          or d.Diagnosis_Code like '1416%'
          or d.Diagnosis_Code like '1418%'
          or d.Diagnosis_Code like '1419%'
          or d.Diagnosis_Code like '1420%'
          or d.Diagnosis_Code like '1421%'
          or d.Diagnosis_Code like '1422%'
          or d.Diagnosis_Code like '1428%'
          or d.Diagnosis_Code like '1429%'
          or d.Diagnosis_Code like '143%'
          or d.Diagnosis_Code like '144%'
          or d.Diagnosis_Code like '1450%'
          or d.Diagnosis_Code like '1451%'
          or d.Diagnosis_Code like '1452%'
          or d.Diagnosis_Code like '1453%'
          or d.Diagnosis_Code like '1454%'
          or d.Diagnosis_Code like '1455%'
          or d.Diagnosis_Code like '1456%'
          or d.Diagnosis_Code like '1458%'
          or d.Diagnosis_Code like '1459%'
          or d.Diagnosis_Code like '1460%'
          or d.Diagnosis_Code like '1461%'
          or d.Diagnosis_Code like '1462%'
          or d.Diagnosis_Code like '1463%'
          or d.Diagnosis_Code like '1464%'
          or d.Diagnosis_Code like '1465%'
          or d.Diagnosis_Code like '1466%'
          or d.Diagnosis_Code like '1467%'
          or d.Diagnosis_Code like '1468%'
          or d.Diagnosis_Code like '1469%'
          or d.Diagnosis_Code like '147%'
          or d.Diagnosis_Code like '1480%'
          or d.Diagnosis_Code like '1481%'
          or d.Diagnosis_Code like '1482%'
          or d.Diagnosis_Code like '1483%'
          or d.Diagnosis_Code like '1488%'
          or d.Diagnosis_Code like '1489%'
          or d.Diagnosis_Code like '149%'
          or d.Diagnosis_Code like '1600%'
          or d.Diagnosis_Code like '1601%'
          or d.Diagnosis_Code like '1602%'
          or d.Diagnosis_Code like '1603%'
          or d.Diagnosis_Code like '1604%'
          or d.Diagnosis_Code like '1605%'
          or d.Diagnosis_Code like '1608%'
          or d.Diagnosis_Code like '1609%'
          or d.Diagnosis_Code like '161%'
          or d.Diagnosis_Code like '1620%'
          or d.Diagnosis_Code like '190%'
          or d.Diagnosis_Code like '1950%'
          or d.Diagnosis_Code like '152%'
          or d.Diagnosis_Code like '153%'
          or d.Diagnosis_Code like '1540%'
          or d.Diagnosis_Code like '1541%'
          or d.Diagnosis_Code like '1890%'
          or d.Diagnosis_Code like '155%'
          or d.Diagnosis_Code like '1560%'
          or d.Diagnosis_Code like '1561%'
          or d.Diagnosis_Code like '1562%'
          or d.Diagnosis_Code like '1568%'
          or d.Diagnosis_Code like '1569%'
          or d.Diagnosis_Code like '1622%'
          or d.Diagnosis_Code like '1623%'
          or d.Diagnosis_Code like '1624%'
          or d.Diagnosis_Code like '1625%'
          or d.Diagnosis_Code like '1628%'
          or d.Diagnosis_Code like '1629%'
          or d.Diagnosis_Code like '165%'
          or d.Diagnosis_Code like '2000%'
          or d.Diagnosis_Code like '2001%'
          or d.Diagnosis_Code like '2002%'
          or d.Diagnosis_Code like '2003%'
          or d.Diagnosis_Code like '2004%'
          or d.Diagnosis_Code like '2005%'
          or d.Diagnosis_Code like '2006%'
          or d.Diagnosis_Code like '2007%'
          or d.Diagnosis_Code like '2008%'
          or d.Diagnosis_Code like '201%'
          or d.Diagnosis_Code like '2020%'
          or d.Diagnosis_Code like '2021%'
          or d.Diagnosis_Code like '2022%'
          or d.Diagnosis_Code like '2024%'
          or d.Diagnosis_Code like '2027%'
          or d.Diagnosis_Code like '2733%'
          or d.Diagnosis_Code like '172%'
          or d.Diagnosis_Code like '2030%'
          or d.Diagnosis_Code like '2031%'
          or d.Diagnosis_Code like '1830%'
          or d.Diagnosis_Code like '157%'
          or d.Diagnosis_Code like '185%'
          or d.Diagnosis_Code in ('20500'
                                 ,'20501'
                                 ,'20502'
                                 ,'20930'
                                 ,'20280'
                                 ,'20281'
                                 ,'20282'
                                 ,'20283'
                                 ,'20284'
                                 ,'20285'
                                 ,'20286'
                                 ,'20287'
                                 ,'20288'
                                 ,'20380'
                                 ,'20382'
                                 ,'20381'
                                 ,'23872'
                                 ,'23873'
                                 ,'23874'
                                 ,'23875'
                                 )
       group by pat.EMPI
               ,pat.Medical_Record_Number
               ,e.Encounter_Number
               ,d.Date
      )

ps: do yourself (and others) a favour and format your queries - it makes it so much easier to read Here's a link to at site that helps you do just that.

CC_DA · May 16, 2016, 9:52pm

Thanks for all the help! One last thing...would the recursive portion of the query produce results faster if indexed somehow? In the first cte (cte0) there are close to 250k records....I stopped the query at 5 hours the first time. I am going to let it run over night and see if it will finish (unless the enterprise DBAs cancel it) .

CC_DA · May 17, 2016, 2:41pm

Changing the first CTE(the one i added) to a temp table reduced the time from 5+ hours to 1min....incredible.

Thanks again!