text: CREATE TABLE #Codesets (
    codeset_id int NOT NULL,
    concept_id bigint NOT NULL
  )
  ;
  
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 6 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4278515)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4278515)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4073010)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4073010)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 8 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (46257658,2615971,4330771,4040606,2615969,2615970,2615744,2615974,2615746,2615976,2615973,2615975,2110046,4073126,45888526,2108725,42628035,2101759,46257625,46257688,46257736,45890647,2211872,42742550,42742549,45888139,4029715,45890260,926905,724946,46257605,2211859,45890639,45890643,2211858,2720687,38003195,4056678,42628728,2211873,42739244,42736837,2110047,4318947,4141157,37111533)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (46257658,4330771,45888526,2108725,2101759,46257625,46257688,46257736,45890647,2211872,42742550,42742549,45888139,4029715,45890260,926905,724946,46257605,2211859,45890639,45890643,2211858,2720687,2211873,42736837,2110047)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 9 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (40482030,4180749)
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 10 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2110046,4073126,4141808,44791407,44808725,2110044,4211496,2110037,4096783,4276520,46270921,44806386,4318947,4141157,937611,4142107,37111533)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (2110046,4073126,4141808,44791407,44808725,2110044,4211496,2110037,4096783,4276520,46270921,44806386,4318947,4141157,937611,4142107,37111533)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 11 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2108678,45768763,937694,21603834,40222431,4162987,1315942,21603823,44816340,953300,1309188,45892579,21603845,1378382,4057754,4178736,45775965,45763838,1718850,40224095,4003224,45889069,2109822,4234536)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (45768763,937694,21603834,40222431,4162987,1315942,21603823,44816340,953300,1309188,45892579,21603845,1378382,4057754,4178736,45775965,45763838,1718850,40224095,4003224,45889069,2109822,4234536)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2109223,2109221,2109046,4306061,4130558,4275133,4042907,2103796,2109206,4022910,4185848,2110180,4187917,4097307,4202217,4084297,4296082,2109666)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (2109223,2109221,2109046,4306061,4130558,4275133,4042907,2103796,2109206,4022910,4185848,2110180,4187917,4097307,4202217,4084297,4296082,2109666)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 12 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2617206,37065295,45889041,44793131,37392634)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (2617206,37065295,45889041,44793131)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 13 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (443392)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (443392)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4112752,4163261,4111921)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4112752,4163261,4111921)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 14 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2108678,45768763,937694,21603834,21603823,2721577,21603845)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (45768763,937694,21603834,21603823,21603845)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 15 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (19195,4,7,2617206,37065295,3002131,45889041,44793131,37392634)
  
  		   
  						 
  				 
  		 
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 16 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4140343,4125696,4120296)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4140343,4125696,4120296)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 17 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4220029,2109550,42628675)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4220029)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 18 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4242411)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (4242411)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (435308,4280571,4128371,4127550,4032296,4127551,432961,4032298,4032295,4126131)
  UNION  select c.concept_id
    from @vocabulary_database_schema.CONCEPT c
    join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
    and ca.ancestor_concept_id in (435308,4280571,4128371,4127550,4032296,4127551,432961,4032298,4032295,4126131)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 19 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (1538812,1538366,1538219,1538775,1539231)
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 20 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (36770582)
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 21 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
  ( 
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (36660206)
  
  ) I
  ) C;
  
  
  with primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) as
  (
  -- Begin Primary Events
  select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM
  (
    select E.person_id, E.start_date, E.end_date,
           row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC) ordinal,
           OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
    FROM 
    (
    -- Begin Condition Occurrence Criteria
  SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, DATEADD(day,1,C.condition_start_date)) as end_date,
         C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* , row_number() over (PARTITION BY co.person_id ORDER BY co.condition_start_date, co.condition_occurrence_id) as ordinal
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets codesets on ((co.condition_concept_id = codesets.concept_id and codesets.codeset_id = 12))
  ) C
  JOIN @cdm_database_schema.PERSON P on C.person_id = P.person_id
  WHERE P.gender_concept_id in (8507)
  AND C.ordinal = 1
  -- End Condition Occurrence Criteria
  
    ) E
  	JOIN @cdm_database_schema.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
    WHERE DATEADD(day,365,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
  ) P
  WHERE P.ordinal = 1
  -- End Primary Events
  
  )
  SELECT event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id
  INTO #qualified_events
  FROM 
  (
    select pe.event_id, pe.person_id, pe.start_date, pe.end_date, pe.op_start_date, pe.op_end_date, row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, cast(pe.visit_occurrence_id as bigint) as visit_occurrence_id
    FROM primary_events pe
    
  ) QE
  
  ;
  
  --- Inclusion Rule Inserts
  
  select 0 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_0
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Demographic Criteria
  SELECT 0 as index_id, e.person_id, e.event_id
  FROM #qualified_events E
  JOIN @cdm_database_schema.PERSON P ON P.PERSON_ID = E.PERSON_ID
  WHERE YEAR(E.start_date) - P.year_of_birth >= 18
  GROUP BY e.person_id, e.event_id
  -- End Demographic Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 1
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 1 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_1
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, DATEADD(d,1,C.procedure_date) as END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.* 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 6))
  ) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) > 0
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 2 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_2
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Condition Occurrence Criteria
  SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, DATEADD(day,1,C.condition_start_date)) as end_date,
         C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets codesets on ((co.condition_concept_id = codesets.concept_id and codesets.codeset_id = 9))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-365,P.START_DATE) AND A.START_DATE <= DATEADD(day,-1,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, DATEADD(d,1,C.observation_date) as END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.* 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 9))
  ) C
  
  WHERE C.value_as_concept_id in (4253628)
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-365,P.START_DATE) AND A.START_DATE <= DATEADD(day,-1,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 2
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 3 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_3
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 10))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 10))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, c.END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.person_id, o.observation_id, o.observation_date, DATEADD(d,1,o.observation_date) as END_DATE, o.observation_concept_id, o.visit_occurrence_id 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 10))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 10))
  }) C
  
  
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 2 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 3 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         C.end_date, C.drug_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.drug_exposure_start_date as sort_date
  from 
  (
    select de.person_id, de.drug_exposure_id, de.drug_exposure_start_date, COALESCE(de.drug_exposure_end_date, DATEADD(day, 1, de.drug_exposure_start_date)) as end_date, de.drug_concept_id, de.visit_occurrence_id 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  }) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 4 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, c.END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.person_id, o.observation_id, o.observation_date, DATEADD(d,1,o.observation_date) as END_DATE, o.observation_concept_id, o.visit_occurrence_id 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  }) C
  
  
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 5 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Device Exposure Criteria
  select C.person_id, C.device_exposure_id as event_id, C.device_exposure_start_date as start_date, C.device_exposure_end_date as end_date,
         C.device_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.device_exposure_start_date as sort_date
  from 
  (
    select de.person_id, de.device_exposure_id, de.device_exposure_start_date, de.device_exposure_end_date, de.device_concept_id, de.visit_occurrence_id
    FROM @cdm_database_schema.DEVICE_EXPOSURE de
  JOIN #Codesets codesets on ((de.device_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 11))
  }) C
  
  
  -- End Device Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 6 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    select PE.person_id, PE.event_id, PE.start_date, PE.end_date, PE.target_concept_id, PE.visit_occurrence_id, PE.sort_date FROM (
  -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) PE
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
  FROM (-- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  ) Q
  JOIN @cdm_database_schema.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
    and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
  ) E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
  FROM (-- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 17))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  ) Q
  JOIN @cdm_database_schema.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
    and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
  ) P
  INNER JOIN
  (
    -- Begin Condition Occurrence Criteria
  SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, DATEADD(day,1,C.condition_start_date)) as end_date,
         C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets codesets on ((co.condition_concept_id = codesets.concept_id and codesets.codeset_id = 18))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-180,P.START_DATE) AND A.START_DATE <= DATEADD(day,0,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 1
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id and AC.event_id = pe.event_id
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 7 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 8 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, c.END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.person_id, o.observation_id, o.observation_date, DATEADD(d,1,o.observation_date) as END_DATE, o.observation_concept_id, o.visit_occurrence_id 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  }) C
  
  
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 9 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Device Exposure Criteria
  select C.person_id, C.device_exposure_id as event_id, C.device_exposure_start_date as start_date, C.device_exposure_end_date as end_date,
         C.device_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.device_exposure_start_date as sort_date
  from 
  (
    select de.person_id, de.device_exposure_id, de.device_exposure_start_date, de.device_exposure_end_date, de.device_concept_id, de.visit_occurrence_id
    FROM @cdm_database_schema.DEVICE_EXPOSURE de
  JOIN #Codesets codesets on ((de.device_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 8))
  }) C
  
  
  -- End Device Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,180,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 10
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 4 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_4
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         C.end_date, C.drug_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.drug_exposure_start_date as sort_date
  from 
  (
    select de.person_id, de.drug_exposure_id, de.drug_exposure_start_date, COALESCE(de.drug_exposure_end_date, DATEADD(day, 1, de.drug_exposure_start_date)) as end_date, de.drug_concept_id, de.visit_occurrence_id 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  }) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-365,P.START_DATE) AND A.START_DATE <= DATEADD(day,-1,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, c.END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.person_id, o.observation_id, o.observation_date, DATEADD(d,1,o.observation_date) as END_DATE, o.observation_concept_id, o.visit_occurrence_id 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  }) C
  
  
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-365,P.START_DATE) AND A.START_DATE <= DATEADD(day,-1,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 2 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, C.END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.person_id, po.procedure_occurrence_id, po.procedure_date,DATEADD(d,1,po.procedure_date) as END_DATE, po.procedure_concept_id, po.visit_occurrence_id 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  --adding episode
  {@episodetable} ? {  union all
    select ep.person_id, ep.episode_id, ep.episode_start_datetime, ep.episode_end_datetime,ep.episode_object_concept_id,null
    from @cdm_database_schema.episode ep
  JOIN #Codesets codesets on ((ep.episode_object_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  }) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-365,P.START_DATE) AND A.START_DATE <= DATEADD(day,1,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 3
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 5 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_5
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Measurement Criteria
  select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
         C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 19))
  ) C
  
  
  -- End Measurement Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Criteria Group
  select 1 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Measurement Criteria
  select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
         C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 20))
  ) C
  
  
  -- End Measurement Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Criteria Group
  
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Measurement Criteria
  select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
         C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 21))
  ) C
  
  WHERE C.value_as_concept_id in (36662377)
  -- End Measurement Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) > 0
  ) G
  -- End Criteria Group
  
  UNION ALL
  -- Begin Criteria Group
  select 2 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Measurement Criteria
  select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
         C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 15))
  ) C
  
  WHERE C.value_as_number < 10.0000
  -- End Measurement Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, DATEADD(d,1,C.observation_date) as END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.* 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 15))
  ) C
  
  WHERE C.value_as_number < 10.0000
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-30,P.START_DATE) AND A.START_DATE <= DATEADD(day,30,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 2
  ) G
  -- End Criteria Group
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 3
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 6 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_6
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, DATEADD(d,1,C.procedure_date) as END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.* 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 6))
  ) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,31,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Measurement Criteria
  select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
         C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 15))
  ) C
  
  
  -- End Measurement Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,1,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 3
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 2 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    -- Begin Visit Occurrence Criteria
  select C.person_id, C.visit_occurrence_id as event_id, C.visit_start_date as start_date, C.visit_end_date as end_date,
         C.visit_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.visit_start_date as sort_date
  from 
  (
    select vo.* 
    FROM @cdm_database_schema.VISIT_OCCURRENCE vo
  
  ) C
  LEFT JOIN @cdm_database_schema.PROVIDER PR on C.provider_id = PR.provider_id
  WHERE PR.specialty_concept_id in (38004474)
  -- End Visit Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,1,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 3
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 3 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  INNER JOIN
  (
    select PE.person_id, PE.event_id, PE.start_date, PE.end_date, PE.target_concept_id, PE.visit_occurrence_id, PE.sort_date FROM (
  -- Begin Visit Occurrence Criteria
  select C.person_id, C.visit_occurrence_id as event_id, C.visit_start_date as start_date, C.visit_end_date as end_date,
         C.visit_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.visit_start_date as sort_date
  from 
  (
    select vo.* 
    FROM @cdm_database_schema.VISIT_OCCURRENCE vo
  
  ) C
  
  
  -- End Visit Occurrence Criteria
  
  ) PE
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
  FROM (-- Begin Visit Occurrence Criteria
  select C.person_id, C.visit_occurrence_id as event_id, C.visit_start_date as start_date, C.visit_end_date as end_date,
         C.visit_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.visit_start_date as sort_date
  from 
  (
    select vo.* 
    FROM @cdm_database_schema.VISIT_OCCURRENCE vo
  
  ) C
  
  
  -- End Visit Occurrence Criteria
  ) Q
  JOIN @cdm_database_schema.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
    and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
  ) E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
  FROM (-- Begin Visit Occurrence Criteria
  select C.person_id, C.visit_occurrence_id as event_id, C.visit_start_date as start_date, C.visit_end_date as end_date,
         C.visit_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.visit_start_date as sort_date
  from 
  (
    select vo.* 
    FROM @cdm_database_schema.VISIT_OCCURRENCE vo
  
  ) C
  
  
  -- End Visit Occurrence Criteria
  ) Q
  JOIN @cdm_database_schema.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
    and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
  ) P
  INNER JOIN
  (
    -- Begin Observation Criteria
  select C.person_id, C.observation_id as event_id, C.observation_date as start_date, DATEADD(d,1,C.observation_date) as END_DATE,
         C.observation_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.observation_date as sort_date
  from 
  (
    select o.* 
    FROM @cdm_database_schema.OBSERVATION o
  JOIN #Codesets codesets on ((o.observation_concept_id = codesets.concept_id and codesets.codeset_id = 16))
  ) C
  
  
  -- End Observation Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,1,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
  FROM (-- Begin Visit Occurrence Criteria
  select C.person_id, C.visit_occurrence_id as event_id, C.visit_start_date as start_date, C.visit_end_date as end_date,
         C.visit_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.visit_start_date as sort_date
  from 
  (
    select vo.* 
    FROM @cdm_database_schema.VISIT_OCCURRENCE vo
  
  ) C
  
  
  -- End Visit Occurrence Criteria
  ) Q
  JOIN @cdm_database_schema.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
    and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
  ) P
  INNER JOIN
  (
    -- Begin Procedure Occurrence Criteria
  select C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, DATEADD(d,1,C.procedure_date) as END_DATE,
         C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.procedure_date as sort_date
  from 
  (
    select po.* 
    FROM @cdm_database_schema.PROCEDURE_OCCURRENCE po
  JOIN #Codesets codesets on ((po.procedure_concept_id = codesets.concept_id and codesets.codeset_id = 16))
  ) C
  
  
  -- End Procedure Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,1,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) > 0
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id and AC.event_id = pe.event_id
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,1,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) >= 3
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) > 0
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  select 7 as inclusion_rule_id, person_id, event_id
  INTO #Inclusion_7
  FROM 
  (
    select pe.person_id, pe.event_id
    FROM #qualified_events pe
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM #qualified_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Death Criteria
  select C.person_id, C.person_id as event_id, C.death_date as start_date, DATEADD(d,1,C.death_date) as end_date,
         coalesce(C.cause_concept_id,0) as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.death_date as sort_date
  from 
  (
    select d.*
    FROM @cdm_database_schema.DEATH d
  
  ) C
  
  
  -- End Death Criteria
  
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,0,P.START_DATE) AND A.START_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM #qualified_events P
  LEFT JOIN
  (
    -- Begin Observation Period Criteria
  select C.person_id, C.observation_period_id as event_id, C.observation_period_start_date as start_date, C.observation_period_end_date as end_date,
         C.period_type_concept_id as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.observation_period_start_date as sort_date
  
  from 
  (
          select op.*, row_number() over (PARTITION BY op.person_id ORDER BY op.observation_period_start_date) as ordinal
          FROM @cdm_database_schema.OBSERVATION_PERIOD op
  ) C
  
  
  -- End Observation Period Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.END_DATE >= DATEADD(day,0,P.START_DATE) AND A.END_DATE <= DATEADD(day,548,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
    ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
    GROUP BY E.person_id, E.event_id
    HAVING COUNT(index_id) = 2
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
  ) Results
  ;
  
  SELECT inclusion_rule_id, person_id, event_id
  INTO #inclusion_events
  FROM (select inclusion_rule_id, person_id, event_id from #Inclusion_0
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_1
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_2
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_3
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_4
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_5
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_6
  UNION ALL
  select inclusion_rule_id, person_id, event_id from #Inclusion_7) I;
  TRUNCATE TABLE #Inclusion_0;
  DROP TABLE #Inclusion_0;
  
  TRUNCATE TABLE #Inclusion_1;
  DROP TABLE #Inclusion_1;
  
  TRUNCATE TABLE #Inclusion_2;
  DROP TABLE #Inclusion_2;
  
  TRUNCATE TABLE #Inclusion_3;
  DROP TABLE #Inclusion_3;
  
  TRUNCATE TABLE #Inclusion_4;
  DROP TABLE #Inclusion_4;
  
  TRUNCATE TABLE #Inclusion_5;
  DROP TABLE #Inclusion_5;
  
  TRUNCATE TABLE #Inclusion_6;
  DROP TABLE #Inclusion_6;
  
  TRUNCATE TABLE #Inclusion_7;
  DROP TABLE #Inclusion_7;
  
  
  with cteIncludedEvents(event_id, person_id, start_date, end_date, op_start_date, op_end_date, ordinal) as
  (
    SELECT event_id, person_id, start_date, end_date, op_start_date, op_end_date, row_number() over (partition by person_id order by start_date ASC) as ordinal
    from
    (
      select Q.event_id, Q.person_id, Q.start_date, Q.end_date, Q.op_start_date, Q.op_end_date, SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) as inclusion_rule_mask
      from #qualified_events Q
      LEFT JOIN #inclusion_events I on I.person_id = Q.person_id and I.event_id = Q.event_id
      GROUP BY Q.event_id, Q.person_id, Q.start_date, Q.end_date, Q.op_start_date, Q.op_end_date
    ) MG -- matching groups
  
    -- the matching group with all bits set ( POWER(2,# of inclusion rules) - 1 = inclusion_rule_mask
    WHERE (MG.inclusion_rule_mask = POWER(cast(2 as bigint),8)-1)
  
  )
  select event_id, person_id, start_date, end_date, op_start_date, op_end_date
  into #included_events
  FROM cteIncludedEvents Results
  
  ;
  
  
  
  -- generate cohort periods into #final_cohort
  with cohort_ends (event_id, person_id, end_date) as
  (
  	-- cohort exit dates
    -- By default, cohort exit at the event's op end date
  select event_id, person_id, op_end_date as end_date from #included_events
  UNION ALL
  -- Censor Events
  select i.event_id, i.person_id, MIN(c.start_date) as end_date
  FROM #included_events i
  JOIN
  (
  -- Begin Death Criteria
  select C.person_id, C.person_id as event_id, C.death_date as start_date, DATEADD(d,1,C.death_date) as end_date,
         coalesce(C.cause_concept_id,0) as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.death_date as sort_date
  from 
  (
    select d.*
    FROM @cdm_database_schema.DEATH d
  
  ) C
  
  
  -- End Death Criteria
  
  
  ) C on C.person_id = I.person_id and C.start_date >= I.start_date and C.START_DATE <= I.op_end_date
  GROUP BY i.event_id, i.person_id
  
  UNION ALL
  select i.event_id, i.person_id, MIN(c.start_date) as end_date
  FROM #included_events i
  JOIN
  (
  -- Begin Condition Occurrence Criteria
  SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, DATEADD(day,1,C.condition_start_date)) as end_date,
         C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id,
         C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets codesets on ((co.condition_concept_id = codesets.concept_id and codesets.codeset_id = 13))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) C on C.person_id = I.person_id and C.start_date >= I.start_date and C.START_DATE <= I.op_end_date
  GROUP BY i.event_id, i.person_id
  
  
  ),
  first_ends (person_id, start_date, end_date) as
  (
  	select F.person_id, F.start_date, F.end_date
  	FROM (
  	  select I.event_id, I.person_id, I.start_date, E.end_date, row_number() over (partition by I.person_id, I.event_id order by E.end_date) as ordinal 
  	  from #included_events I
  	  join cohort_ends E on I.event_id = E.event_id and I.person_id = E.person_id and E.end_date >= I.start_date
  	) F
  	WHERE F.ordinal = 1
  )
  select person_id, start_date, end_date
  INTO #cohort_rows
  from first_ends;
  
  with cteEndDates (person_id, end_date) AS -- the magic
  (	
  	SELECT
  		person_id
  		, DATEADD(day,-1 * 0, event_date)  as end_date
  	FROM
  	(
  		SELECT
  			person_id
  			, event_date
  			, event_type
  			, MAX(start_ordinal) OVER (PARTITION BY person_id ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal 
  			, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY event_date, event_type) AS overall_ord
  		FROM
  		(
  			SELECT
  				person_id
  				, start_date AS event_date
  				, -1 AS event_type
  				, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY start_date) AS start_ordinal
  			FROM #cohort_rows
  		
  			UNION ALL
  		
  
  			SELECT
  				person_id
  				, DATEADD(day,0,end_date) as end_date
  				, 1 AS event_type
  				, NULL
  			FROM #cohort_rows
  		) RAWDATA
  	) e
  	WHERE (2 * e.start_ordinal) - e.overall_ord = 0
  ),
  cteEnds (person_id, start_date, end_date) AS
  (
  	SELECT
  		 c.person_id
  		, c.start_date
  		, MIN(e.end_date) AS end_date
  	FROM #cohort_rows c
  	JOIN cteEndDates e ON c.person_id = e.person_id AND e.end_date >= c.start_date
  	GROUP BY c.person_id, c.start_date
  )
  select person_id, min(start_date) as start_date, end_date
  into #final_cohort
  from cteEnds
  group by person_id, end_date
  ;
  
  DELETE FROM @target_database_schema.@target_cohort_table where cohort_definition_id = @target_cohort_id;
  INSERT INTO @target_database_schema.@target_cohort_table (cohort_definition_id, subject_id, cohort_start_date, cohort_end_date)
  select @target_cohort_id as cohort_definition_id, person_id, DATEADD(day,548,start_date) as start_date, end_date 
  FROM #final_cohort CO
  ;
  
  
  
  
  
  TRUNCATE TABLE #cohort_rows;
  DROP TABLE #cohort_rows;
  
  TRUNCATE TABLE #final_cohort;
  DROP TABLE #final_cohort;
  
  TRUNCATE TABLE #inclusion_events;
  DROP TABLE #inclusion_events;
  
  TRUNCATE TABLE #qualified_events;
  DROP TABLE #qualified_events;
  
  TRUNCATE TABLE #included_events;
  DROP TABLE #included_events;
  
  TRUNCATE TABLE #Codesets;
  DROP TABLE #Codesets;