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 (37397537,432870)
  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 (37397537,432870)
    and c.invalid_reason is null
  
  ) I
  ) C UNION ALL 
  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 (3007461,3031586,3024929,3039827,3024386,4267147,37393863)
  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 (3007461,3031586,3024929,3039827,3024386,4267147,37393863)
    and c.invalid_reason is null
  
  ) I
  ) C UNION ALL 
  SELECT 31 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 (44782752,435565,40481089,193512,4133004,4149782,44782762,762049,762048,46271900,4317289,4124856,4318407,4055089,45757410,45757409,4203836,44782759,4205652,762046,762051,44811347,4033521,45773536,4203618,762047,36712892,4096099)
  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 (44782752,40481089,4133004)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (36712971,435887,4179911,435031,4062264,438820,4089917,4269051)
  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 (36712971,435887,435031,4062264,438820)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 32 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 (4102202,4111714)
  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 (4102202,4111714)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4062269,4066234,4061473,4273550,36717594,45765438)
  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 (4062269,4066234,4061473,4273550,36717594,45765438)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 34 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 (320741,4096099,44782752,44811347,45757409,4203836,4205652,4124856,4203618,4318407,762047,44782762,435565,45757410,4033521,762049,4055089,440738,444247,40481089,193512,45773536,36712892,4234997,439838,4133004,44782759,444097,4327889,4149782,762048,4317289)
  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 (44782752,40481089,4133004)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (36712971,435887,438820,4179911,4089917)
  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 (36712971,435887,438820,4089917)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 35 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 (254662,36713113,440417,4236271)
  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 (254662,36713113,440417)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (435616,435887,196715,4062269,442055,433832,435026,440477,318137,199837,438820,4235812,195294,4187790,444247,44782732,40479606,4240832)
  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 (435616,435887,196715,4062269,442055,433832,435026,440477,318137,199837,438820,4235812,195294,4187790,444247,44782732,40479606)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 36 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 (4223098,4301208,4111853,196715)
  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 (4223098,4301208,4111853)
    and c.invalid_reason is null
  
  ) I
  ) C UNION ALL 
  SELECT 37 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 (36712891,4033521,35615064,36712892)
  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 (36712891,4033521,35615064,36712892)
    and c.invalid_reason is null
  
  ) I
  ) C UNION ALL 
  SELECT 38 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 (4049318,199837,37111372,4223670,196715)
  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 (4049318,199837,37111372,4223670)
    and c.invalid_reason is null
  
  ) I
  ) C UNION ALL 
  SELECT 39 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 (4207615,4273526,4223670,444264)
  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 (4207615,4273526,4223670,444264)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (195294)
  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 (195294)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 40 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 (443454)
  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 (443454)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (40479572,4046360,372435,377254,379778,443790,443864,444091,4046089,4046090,4129534)
  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 (40479572,4046360)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 41 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 (439847,432923,4148906,4144154,4111709,42535426,43530728,43530727,4108952,4111708,35609033)
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (376713,4174299,36716544,4345688)
  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 (4174299,36716544,4345688)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 43 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 (4329847,4108680,4108678,438172,4124687,45766212,37109910,37109911,37109912)
  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 (4329847,4108680,4108678,438172,4124687,45766212,37109910,37109911,37109912)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (314666)
  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 (314666)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 44 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 (4195665,4148299,4173167,4317289,4319280,4144032,45757410,45757409,44811741,44811740,37117790,37016198,35622081,35622080,4345926,4342767,4341648,4341646,4340939,4340378,4340375,4318537,4318407,4240850,4239942,4237654,4215949,4214720,4192856,4188336,4174014,4149013,4148257,4148256,4124856,4055089,4055025,4045408,201894,192673)
  
  ) I
  ) C UNION ALL 
  SELECT 45 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 (4048527,4044745)
  
  ) I
  ) C UNION ALL 
  SELECT 46 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 (4124856,4318407,4055089,45757410,45757409,4317289,4144032,4173167,36717492)
  
  ) I
  ) C UNION ALL 
  SELECT 47 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 (432870)
  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 (432870)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (37397537,36713112,40321716,435076,4239484,137829,4133981,436956,4316372,42536958,4247776,440982,42537688,437242,4221109,432881,4119134)
  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 (37397537,36713112,435076,4239484,137829,4133981,436956,4316372,42536958,4247776,440982,42537688,437242,4221109,432881)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) C UNION ALL 
  SELECT 48 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 (4102202,318137,4111714,4045733)
  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 (4102202,318137,4111714)
    and c.invalid_reason is null
  
  ) I
  LEFT JOIN
  (
    select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4062269,4066234,4061473,4273550,45765438,36717594)
  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 (4062269,4066234,4061473,4273550,45765438,36717594)
    and c.invalid_reason is null
  
  ) E ON I.concept_id = E.concept_id
  WHERE E.concept_id is null
  ) 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 32)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 31)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 35)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 36)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 37)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 38)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 39)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 40)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 41)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 43)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 44)
  ) C
  
  
  -- End Condition Occurrence Criteria
  
  UNION ALL
  -- 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,
    CAST(NULL as bigint) as visit_occurrence_id, C.death_date as sort_date
  from 
  (
    select d.*
    FROM @cdm_database_schema.DEATH d
  JOIN #Codesets cs on (d.cause_concept_id = cs.concept_id and cs.codeset_id = 45)
  ) C
  
  
  -- End Death Criteria
  
  
  UNION ALL
  -- 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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 46)
  ) C
  
  
  -- 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,0,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
  ) P
  
  -- 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 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, cc.person_id, cc.event_id
  from (SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets cs on (m.measurement_concept_id = cs.concept_id and cs.codeset_id = 2)
  ) C
  
  WHERE (C.value_as_number >= 10.0000 and C.value_as_number <= 150.0000)
  AND C.unit_concept_id in (8686,8785,8848,8961,9444,9254,8816)
  -- 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,-7,P.START_DATE) AND A.START_DATE <= DATEADD(day,7,P.START_DATE) ) cc 
  GROUP BY cc.person_id, cc.event_id
  HAVING COUNT(cc.event_id) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  select 1 as index_id, cc.person_id, cc.event_id
  from (SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets cs on (m.measurement_concept_id = cs.concept_id and cs.codeset_id = 2)
  ) C
  
  WHERE (C.value_as_number >= 10.0000 and C.value_as_number <= 150.0000)
  AND (C.range_low >= 130.0000 and C.range_low <= 170.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,-7,P.START_DATE) AND A.START_DATE <= DATEADD(day,7,P.START_DATE) ) cc 
  GROUP BY cc.person_id, cc.event_id
  HAVING COUNT(cc.event_id) >= 1
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  select 2 as index_id, cc.person_id, cc.event_id
  from (SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 1)
  ) 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,-7,P.START_DATE) AND A.START_DATE <= DATEADD(day,7,P.START_DATE) ) cc 
  GROUP BY cc.person_id, cc.event_id
  HAVING COUNT(cc.event_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 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
  LEFT JOIN (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets cs on (m.measurement_concept_id = cs.concept_id and cs.codeset_id = 2)
  ) C
  
  WHERE (C.value_as_number >= 10.0000 and C.value_as_number <= 150.0000)
  AND C.unit_concept_id in (8686,8785,8848,8961,9444,9254,45890995)
  -- 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,-90,P.START_DATE) AND A.START_DATE <= DATEADD(day,-7,P.START_DATE) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.measurement_date as sort_date
  from 
  (
    select m.* 
    FROM @cdm_database_schema.MEASUREMENT m
  JOIN #Codesets cs on (m.measurement_concept_id = cs.concept_id and cs.codeset_id = 2)
  ) C
  
  WHERE (C.value_as_number >= 10.0000 and C.value_as_number <= 150.0000)
  AND (C.range_low >= 130.0000 and C.range_low <= 170.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,-90,P.START_DATE) AND A.START_DATE <= DATEADD(day,-7,P.START_DATE) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 1)
  ) 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,-90,P.START_DATE) AND A.START_DATE <= DATEADD(day,-7,P.START_DATE) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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
  
    ) 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 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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 48)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 34)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 35)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 36)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 37)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 38)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 39)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 40)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 41)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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 (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 44)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_id) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  select 10 as index_id, p.person_id, p.event_id
  from #qualified_events p
  LEFT JOIN (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 43)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_id) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  select 11 as index_id, p.person_id, p.event_id
  from #qualified_events p
  LEFT JOIN (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 45)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_id) = 0
  -- End Correlated Criteria
  
  UNION ALL
  -- Begin Correlated Criteria
  select 12 as index_id, p.person_id, p.event_id
  from #qualified_events p
  LEFT JOIN (
  SELECT p.person_id, p.event_id 
  FROM #qualified_events P
  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.visit_occurrence_id, C.condition_start_date as sort_date
  FROM 
  (
    SELECT co.* 
    FROM @cdm_database_schema.CONDITION_OCCURRENCE co
    JOIN #Codesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 46)
  ) 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) ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
  GROUP BY p.person_id, p.event_id
  HAVING COUNT(cc.event_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) = 13
  ) 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) I;
  TRUNCATE TABLE #Inclusion_0;
  DROP TABLE #Inclusion_0;
  
  TRUNCATE TABLE #Inclusion_1;
  DROP TABLE #Inclusion_1;
  
  
  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),2)-1)
  
  )
  select event_id, person_id, start_date, end_date, op_start_date, op_end_date
  into #included_events
  FROM cteIncludedEvents Results
  
  ;
  
  -- date offset strategy
  
  select event_id, person_id, 
    case when DATEADD(day,1,start_date) > op_end_date then op_end_date else DATEADD(day,1,start_date) end as end_date
  INTO #strategy_ends
  from #included_events;
  
  
  -- generate cohort periods into #final_cohort
  with cohort_ends (event_id, person_id, end_date) as
  (
  	-- cohort exit dates
    -- End Date Strategy
  SELECT event_id, person_id, end_date from #strategy_ends
  
  ),
  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
  ;
  
  
  
  
  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;
  