text: CREATE TABLE #Codesets (
    codeset_id int NOT NULL,
    concept_id bigint NOT NULL
  )
  ;
  
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 1 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 (43013884,40239216,40166035,1580747,19122137)
  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 (43013884,40239216,40166035,1580747,19122137)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 2 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 (44816332,45774435,1583722,40170911,44506754,793143)
  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 (44816332,45774435,1583722,40170911,44506754,793143)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 3 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 (43526465,44785829,45774751,793293)
  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 (43526465,44785829,45774751,793293)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 4 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 (1594973,1597756,1560171,19097821,1559684,1502809,1502855)
  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 (1594973,1597756,1560171,19097821,1559684,1502809,1502855)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 5 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 (1529331,1530014,730548,19033498,19001409,19059796,19001441,1510202,1502826,1525215,1516766,1547504,1515249)
  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 (1529331,1530014,730548,19033498,19001409,19059796,19001441,1510202,1502826,1525215,1516766,1547504,1515249)
    and c.invalid_reason is null
  
  ) I
  ) C;
  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 (1596977,1550023,1567198,1502905,1513876,1531601,1586346,1544838,1516976,1590165,1513849,1562586,1588986,1513843,1586369,35605670,35602717,21600713,19078608)
  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 (1596977,1550023,1567198,1502905,1513876,1531601,1586346,1544838,1516976,1590165,1513849,1562586,1588986,1513843,1586369,35605670,35602717,21600713,19078608)
    and c.invalid_reason is null
  
  ) I
  ) C;
  INSERT INTO #Codesets (codeset_id, concept_id)
  SELECT 7 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 (1503297)
  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 (1503297)
    and c.invalid_reason is null
  
  ) I
  ) 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 (319844,321318,312337,4278217,40484167,318443,314659,40479625,372924,376713,381591,315286,44782819,316995,134057,442774,439847,434056,4146311,4329847,4296029,317309,321822,313928,321052,44782775,441039,4067424,443239,4318842,432923,439040,4141106,4194610,4189293)
  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 (319844,321318,312337,4278217,40484167,318443,40479625,372924,376713,381591,315286,44782819,316995,442774,439847,434056,4146311,4329847,4296029,317309,321822,313928,44782775,441039,4067424,443239,4318842,432923,439040,4141106,4194610,4189293)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4124841,40484541,312902,4288310,316494,4313767,372721,40480453,46272492,4324690,441246,380113,192763,4275428,260841,318137,320749,440417,380943,320741,4132546,318169,443752,432346)
  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 (4124841,40484541,312902,4288310,316494,4313767,372721,40480453,46272492,4324690,441246,380113,192763,4275428,260841,318137,320749,440417,380943,320741,4132546,318169,443752,432346)
    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 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 (4150819,4331725)
  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 (4150819,4331725)
    and c.invalid_reason is null
  
  ) 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 (195771)
  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 (195771)
    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 (201254,435216,200687,377821,318712)
  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 (201254,435216,200687,377821,318712)
    and c.invalid_reason is null
  
  ) I
  ) 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 (201826,443734,443767,192279,443735,376065,443729,443732)
  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 (201826,443734,443767,192279,443735,376065,443729,443732)
    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 (4030518)
  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 (4030518)
    and c.invalid_reason is null
  
  ) I
  ) 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 (44816332,43013884,1594973,45774435,1583722,1597756,1560171,19097821,1559684,40239216,40170911,44506754,40166035,793143,1580747,1502809,1502855,19122137)
  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 (44816332,43013884,1594973,45774435,1583722,1597756,1560171,19097821,1559684,40239216,40170911,44506754,40166035,793143,1580747,1502809,1502855,19122137)
    and c.invalid_reason is null
  
  ) 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 Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* , row_number() over (PARTITION BY de.person_id ORDER BY de.drug_exposure_start_date, de.drug_exposure_id) as ordinal
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 3))
  ) C
  
  WHERE C.ordinal = 1
  -- End Drug Exposure 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
    
  JOIN (
  -- Begin Criteria Group
  select 0 as index_id, person_id, event_id
  FROM
  (
    select E.person_id, E.event_id 
    FROM primary_events E
    INNER JOIN
    (
      -- Begin Correlated Criteria
  SELECT 0 as index_id, p.person_id, p.event_id
  FROM primary_events 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 = 12))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) A on A.person_id = P.person_id  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
  
  UNION ALL
  -- Begin Correlated Criteria
  SELECT 1 as index_id, p.person_id, p.event_id
  FROM primary_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 = 11))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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 primary_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 = 10))
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,P.START_DATE)
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(A.TARGET_CONCEPT_ID) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Demographic Criteria
  SELECT 3 as index_id, e.person_id, e.event_id
  FROM primary_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) = 4
  ) G
  -- End Criteria Group
  ) AC on AC.person_id = pe.person_id and AC.event_id = pe.event_id
  
  ) QE
  WHERE QE.ordinal = 1
  ;
  
  --- 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 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,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 1))
  ) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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) = 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
  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,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 2))
  ) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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) = 1
  ) 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 Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 4))
  ) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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) = 1
  ) 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 Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 5))
  ) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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) = 1
  ) 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 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
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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) = 1
  ) 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 Drug Era Criteria
  select C.person_id, C.drug_era_id as event_id, C.drug_era_start_date as start_date, C.drug_era_end_date as end_date,
         C.drug_concept_id as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.drug_era_start_date as sort_date
  from 
  (
    select de.* 
    FROM @cdm_database_schema.DRUG_ERA de
  where de.drug_concept_id in (SELECT concept_id from  #Codesets where codeset_id = 7)
  ) C
  
  WHERE DATEDIFF(d,C.drug_era_start_date, C.drug_era_end_date) >= 90
  -- End Drug Era Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,-90,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 Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 7))
  ) C
  
  
  -- End Drug Exposure Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,0,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 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
  LEFT JOIN
  (
    -- Begin Drug Era Criteria
  select C.person_id, C.drug_era_id as event_id, C.drug_era_start_date as start_date, C.drug_era_end_date as end_date,
         C.drug_concept_id as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.drug_era_start_date as sort_date
  from 
  (
    select de.* 
    FROM @cdm_database_schema.DRUG_ERA de
  where de.drug_concept_id in (SELECT concept_id from  #Codesets where codeset_id = 6)
  ) C
  
  WHERE DATEDIFF(d,C.drug_era_start_date, C.drug_era_end_date) > 30
  -- End Drug Era Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE <= DATEADD(day,-30,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 Drug Era Criteria
  select C.person_id, C.drug_era_id as event_id, C.drug_era_start_date as start_date, C.drug_era_end_date as end_date,
         C.drug_concept_id as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.drug_era_start_date as sort_date
  from 
  (
    select de.* 
    FROM @cdm_database_schema.DRUG_ERA de
  where de.drug_concept_id in (SELECT concept_id from  #Codesets where codeset_id = 6)
  ) C
  
  
  -- End Drug Era Criteria
  
  ) A on A.person_id = P.person_id  AND A.START_DATE >= DATEADD(day,-30,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) = 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) 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;
  
  
  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),7)-1)
  
  )
  select event_id, person_id, start_date, end_date, op_start_date, op_end_date
  into #included_events
  FROM cteIncludedEvents Results
  WHERE Results.ordinal = 1
  ;
  
  -- custom era strategy
  
  with ctePersons(person_id) as (
  	select distinct person_id from #included_events
  )
  
  select person_id, drug_exposure_start_date, drug_exposure_end_date
  INTO #drugTarget
  FROM (
  	select de.PERSON_ID, DRUG_EXPOSURE_START_DATE,  COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE 
  	FROM @cdm_database_schema.DRUG_EXPOSURE de
  	JOIN ctePersons p on de.person_id = p.person_id
  	JOIN #Codesets cs on cs.codeset_id = 3 AND de.drug_concept_id = cs.concept_id
  
  	UNION ALL
  
  	select de.PERSON_ID, DRUG_EXPOSURE_START_DATE,  COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE 
  	FROM @cdm_database_schema.DRUG_EXPOSURE de
  	JOIN ctePersons p on de.person_id = p.person_id
  	JOIN #Codesets cs on cs.codeset_id = 3 AND de.drug_source_concept_id = cs.concept_id
  ) E
  ;
  
  select et.event_id, et.person_id, ERAS.era_end_date as end_date
  INTO #strategy_ends
  from #included_events et
  JOIN 
  (
    select ENDS.person_id, min(drug_exposure_start_date) as era_start_date, DATEADD(day,0, ENDS.era_end_date) as era_end_date
    from
    (
      select de.person_id, de.drug_exposure_start_date, MIN(e.END_DATE) as era_end_date
      FROM #drugTarget DE
      JOIN 
      (
        --cteEndDates
        select PERSON_ID, DATEADD(day,-1 * 30,EVENT_DATE) as END_DATE -- unpad the end date by 30
        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 -- this re-numbers the inner UNION so all rows are numbered ordered by the event date
  				from
  				(
  					-- select the start dates, assigning a row number to each
  					Select PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, 0 as EVENT_TYPE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL
  					from #drugTarget D
  
  					UNION ALL
  
  					-- add the end dates with NULL as the row number, padding the end dates by 30 to allow a grace period for overlapping ranges.
  					select PERSON_ID, DATEADD(day,30,DRUG_EXPOSURE_END_DATE), 1 as EVENT_TYPE, NULL
  					FROM #drugTarget D
  				) RAWDATA
        ) E
        WHERE 2 * E.START_ORDINAL - E.OVERALL_ORD = 0
      ) E on DE.PERSON_ID = E.PERSON_ID and E.END_DATE >= DE.DRUG_EXPOSURE_START_DATE
      GROUP BY de.person_id, de.drug_exposure_start_date
    ) ENDS
    GROUP BY ENDS.person_id, ENDS.era_end_date
  ) ERAS on ERAS.person_id = et.person_id 
  WHERE et.start_date between ERAS.era_start_date and ERAS.era_end_date;
  
  TRUNCATE TABLE #drugTarget;
  DROP TABLE #drugTarget;
  
  
  -- 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
  -- End Date Strategy
  SELECT event_id, person_id, end_date from #strategy_ends
  
  UNION ALL
  -- Censor Events
  select i.event_id, i.person_id, MIN(c.start_date) as end_date
  FROM #included_events i
  JOIN
  (
  -- Begin Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 14))
  ) C
  
  
  -- End Drug Exposure 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 Drug Exposure Criteria
  select C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
         COALESCE(C.drug_exposure_end_date, DATEADD(day, 1, C.drug_exposure_start_date)) as 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.* 
    FROM @cdm_database_schema.DRUG_EXPOSURE de
  JOIN #Codesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 5))
  ) C
  
  
  -- End Drug Exposure 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 Drug Era Criteria
  select C.person_id, C.drug_era_id as event_id, C.drug_era_start_date as start_date, C.drug_era_end_date as end_date,
         C.drug_concept_id as TARGET_CONCEPT_ID, CAST(NULL as bigint) as visit_occurrence_id,
         C.drug_era_start_date as sort_date
  from 
  (
    select de.* 
    FROM @cdm_database_schema.DRUG_ERA de
  where de.drug_concept_id in (SELECT concept_id from  #Codesets where codeset_id = 6)
  ) C
  
  WHERE DATEDIFF(d,C.drug_era_start_date, C.drug_era_end_date) > 30
  -- End Drug Era 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, start_date, end_date 
  FROM #final_cohort CO
  ;
  
  
  -- Find the event that is the 'best match' per person.  
  -- the 'best match' is defined as the event that satisfies the most inclusion rules.
  -- ties are solved by choosing the event that matches the earliest inclusion rule, and then earliest.
  
  select q.person_id, q.event_id
  into #best_events
  from #qualified_events Q
  join (
  	SELECT R.person_id, R.event_id, ROW_NUMBER() OVER (PARTITION BY R.person_id ORDER BY R.rule_count DESC,R.min_rule_id ASC, R.start_date ASC) AS rank_value
  	FROM (
  		SELECT Q.person_id, Q.event_id, COALESCE(COUNT(DISTINCT I.inclusion_rule_id), 0) AS rule_count, COALESCE(MIN(I.inclusion_rule_id), 0) AS min_rule_id, Q.start_date
  		FROM #qualified_events Q
  		LEFT JOIN #inclusion_events I ON q.person_id = i.person_id AND q.event_id = i.event_id
  		GROUP BY Q.person_id, Q.event_id, Q.start_date
  	) R
  ) ranked on Q.person_id = ranked.person_id and Q.event_id = ranked.event_id
  WHERE ranked.rank_value = 1
  ;
  
  -- modes of generation: (the same tables store the results for the different modes, identified by the mode_id column)
  -- 0: all events
  -- 1: best event
  
  
  -- BEGIN: Inclusion Impact Analysis - event
  -- calculte matching group counts
  delete from @results_database_schema.cohort_inclusion_result where cohort_definition_id = @target_cohort_id and mode_id = 0;
  insert into @results_database_schema.cohort_inclusion_result (cohort_definition_id, inclusion_rule_mask, person_count, mode_id)
  select @target_cohort_id as cohort_definition_id, inclusion_rule_mask, count_big(*) as person_count, 0 as mode_id
  from
  (
    select Q.person_id, Q.event_id, CAST(SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) AS bigint) as inclusion_rule_mask
    from #qualified_events Q
    LEFT JOIN #inclusion_events I on q.person_id = i.person_id and q.event_id = i.event_id
    GROUP BY Q.person_id, Q.event_id
  ) MG -- matching groups
  group by inclusion_rule_mask
  ;
  
  -- calculate gain counts 
  delete from @results_database_schema.cohort_inclusion_stats where cohort_definition_id = @target_cohort_id and mode_id = 0;
  insert into @results_database_schema.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)
  select ir.cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 0 as mode_id
  from @results_database_schema.cohort_inclusion ir
  left join
  (
    select i.inclusion_rule_id, count_big(i.event_id) as person_count
    from #qualified_events Q
    JOIN #inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id
    group by i.inclusion_rule_id
  ) T on ir.rule_sequence = T.inclusion_rule_id
  CROSS JOIN (select count(*) as total_rules from @results_database_schema.cohort_inclusion where cohort_definition_id = @target_cohort_id) RuleTotal
  CROSS JOIN (select count_big(event_id) as total from #qualified_events) EventTotal
  LEFT JOIN @results_database_schema.cohort_inclusion_result SR on SR.mode_id = 0 AND SR.cohort_definition_id = @target_cohort_id AND (POWER(cast(2 as bigint),RuleTotal.total_rules) - POWER(cast(2 as bigint),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule' 
  WHERE ir.cohort_definition_id = @target_cohort_id
  ;
  
  -- calculate totals
  delete from @results_database_schema.cohort_summary_stats where cohort_definition_id = @target_cohort_id and mode_id = 0;
  insert into @results_database_schema.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)
  select @target_cohort_id as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 0 as mode_id
  FROM
  (select count_big(event_id) as total from #qualified_events) PC,
  (select sum(sr.person_count) as total
    from @results_database_schema.cohort_inclusion_result sr
    CROSS JOIN (select count(*) as total_rules from @results_database_schema.cohort_inclusion where cohort_definition_id = @target_cohort_id) RuleTotal
    where sr.mode_id = 0 and sr.cohort_definition_id = @target_cohort_id and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
  ) FC
  ;
  
  -- END: Inclusion Impact Analysis - event
  
  -- BEGIN: Inclusion Impact Analysis - person
  -- calculte matching group counts
  delete from @results_database_schema.cohort_inclusion_result where cohort_definition_id = @target_cohort_id and mode_id = 1;
  insert into @results_database_schema.cohort_inclusion_result (cohort_definition_id, inclusion_rule_mask, person_count, mode_id)
  select @target_cohort_id as cohort_definition_id, inclusion_rule_mask, count_big(*) as person_count, 1 as mode_id
  from
  (
    select Q.person_id, Q.event_id, CAST(SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) AS bigint) as inclusion_rule_mask
    from #best_events Q
    LEFT JOIN #inclusion_events I on q.person_id = i.person_id and q.event_id = i.event_id
    GROUP BY Q.person_id, Q.event_id
  ) MG -- matching groups
  group by inclusion_rule_mask
  ;
  
  -- calculate gain counts 
  delete from @results_database_schema.cohort_inclusion_stats where cohort_definition_id = @target_cohort_id and mode_id = 1;
  insert into @results_database_schema.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)
  select ir.cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 1 as mode_id
  from @results_database_schema.cohort_inclusion ir
  left join
  (
    select i.inclusion_rule_id, count_big(i.event_id) as person_count
    from #best_events Q
    JOIN #inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id
    group by i.inclusion_rule_id
  ) T on ir.rule_sequence = T.inclusion_rule_id
  CROSS JOIN (select count(*) as total_rules from @results_database_schema.cohort_inclusion where cohort_definition_id = @target_cohort_id) RuleTotal
  CROSS JOIN (select count_big(event_id) as total from #best_events) EventTotal
  LEFT JOIN @results_database_schema.cohort_inclusion_result SR on SR.mode_id = 1 AND SR.cohort_definition_id = @target_cohort_id AND (POWER(cast(2 as bigint),RuleTotal.total_rules) - POWER(cast(2 as bigint),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule' 
  WHERE ir.cohort_definition_id = @target_cohort_id
  ;
  
  -- calculate totals
  delete from @results_database_schema.cohort_summary_stats where cohort_definition_id = @target_cohort_id and mode_id = 1;
  insert into @results_database_schema.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)
  select @target_cohort_id as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id
  FROM
  (select count_big(event_id) as total from #best_events) PC,
  (select sum(sr.person_count) as total
    from @results_database_schema.cohort_inclusion_result sr
    CROSS JOIN (select count(*) as total_rules from @results_database_schema.cohort_inclusion where cohort_definition_id = @target_cohort_id) RuleTotal
    where sr.mode_id = 1 and sr.cohort_definition_id = @target_cohort_id and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
  ) FC
  ;
  
  -- END: Inclusion Impact Analysis - person
  
  -- BEGIN: Censored Stats
  
  -- END: Censored Stats
  
  TRUNCATE TABLE #best_events;
  DROP TABLE #best_events;
  
  
  
  TRUNCATE TABLE #strategy_ends;
  DROP TABLE #strategy_ends;
  
  
  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;
  