/* Need to replace  
@vocabulary_database_schema
@cdm_database_schema
@target_database_schema 
*/

select *
from @target_database_schema.bmi_icd
order by person_id,age_days;

CREATE TABLE #era (
  person_id						        VARCHAR(10)	  	NOT NULL ,
  entry_name			  VARCHAR(255)	NOT NULL ,
  age_days		  INTEGER		NOT NULL
)
;

CREATE TABLE #tenday (
  person_id						        VARCHAR(10)	  	NOT NULL ,
  entry_name			  VARCHAR(255)	NOT NULL ,
  age_days		  INTEGER		NOT NULL
)
;

CREATE TABLE #occur (
  person_id						        VARCHAR(10)	  	NOT NULL ,
  entry_name			  VARCHAR(255)	NOT NULL ,
  age_days		  INTEGER		NOT NULL
)
;

insert into #era( person_id,entry_name,age_days)
select person_id,entry_name,age_days
from @target_database_schema.bmi_covariates
where entry_name like '%era'
or (entry_name not like '%era' and entry_name not like '%occurrence'
    and entry_name not like '%ten_day');

insert into #tenday( person_id,entry_name,age_days)
select person_id,entry_name,age_days
from @target_database_schema.bmi_covariates
where entry_name = 'oral_steroids_ten_day';

insert into #occur( person_id,entry_name,age_days)
select person_id,entry_name,age_days
from @target_database_schema.bmi_covariates
where entry_name like '%occurrence';

select person_id,entry_name,age_days
from #era
union 
select person_id,entry_name,age_days
from #tenday
union
select a.person_id,a.entry_name,a.age_days
from #occur a
left outer join #era b on b.person_id = a.person_id
     and substring(b.entry_name,1,4) = substring(a.entry_name,1,4)
left outer join #tenday c on c.person_id = a.person_id
     and substring(c.entry_name,1,4) = substring(a.entry_name,1,4)
where b.person_id is null
or c.person_id is null
order by person_id,entry_name,age_days;