/*
PRE-REQUISITE TABLES
* required pre-requisite tables in the PCORNETSTUDIES schema
    * TBL_CDC_BMI: the CDC-released BMI for age charts.  (https://www.cdc.gov/growthcharts/charts.htm)
    * TBL_WHO_BMI: the WHO-released BMI for age charts. (https://www.who.int/childgrowth/standards/bmi_for_age/en/)
    * TBL_CDC_LENAGE: the CDC-released length for age charts (https://www.cdc.gov/growthcharts/charts.htm)
    * TBL_CDC_STATUREAGE: the CDC-released stature for age charts (https://www.cdc.gov/growthcharts/charts.htm)
    * TBL_EXCL_CODES: diagnosis codes (ICD-9 and ICD-10) to be used as exclusion criteria
* for all BMI, length, and stature tables, sex 2 = female and sex 1 = male
* data for tables included as a multi-tabbed excel file
*/

CREATE TABLE PCORNETSTUDIES.TBL_CDC_BMI
(
  SEX       VARCHAR2(3 BYTE),
  AGEMOS    NUMBER,
  L         NUMBER,
  M         NUMBER,
  S         NUMBER,
  P3        NUMBER,
  P5        NUMBER,
  P10       NUMBER,
  P25       NUMBER,
  P50       NUMBER,
  P75       NUMBER,
  P85       NUMBER,
  P90       NUMBER,
  P95       NUMBER,
  P97       NUMBER,
  AGE_HIGH  NUMBER
);


CREATE TABLE PCORNETSTUDIES.TBL_CDC_LENAGE
(
  SEX     VARCHAR2(30 BYTE),
  AGEMOS  NUMBER,
  L       NUMBER,
  M       NUMBER,
  S       NUMBER,
  P3      NUMBER,
  P5      NUMBER,
  P10     NUMBER,
  P25     NUMBER,
  P50     NUMBER,
  P75     NUMBER,
  P90     NUMBER,
  P95     NUMBER,
  P97     NUMBER,
  PUB3    NUMBER,
  PUB5    NUMBER,
  PUB10   NUMBER,
  PUB25   NUMBER,
  PUB50   NUMBER,
  PUB75   NUMBER,
  PUB90   NUMBER,
  PUB95   NUMBER,
  PUB97   NUMBER,
  DIFF3   NUMBER,
  DIFF5   NUMBER,
  DIFF10  NUMBER,
  DIFF25  NUMBER,
  DIFF50  NUMBER,
  DIFF75  NUMBER,
  DIFF90  NUMBER,
  DIFF95  NUMBER,
  DIFF97  NUMBER
);


CREATE TABLE PCORNETSTUDIES.TBL_CDC_STATUREAGE
(
  SEX     VARCHAR2(30 BYTE),
  AGEMOS  NUMBER,
  L       NUMBER,
  M       NUMBER,
  S       NUMBER,
  P3      NUMBER,
  P5      NUMBER,
  P10     NUMBER,
  P25     NUMBER,
  P50     NUMBER,
  P75     NUMBER,
  P90     NUMBER,
  P95     NUMBER,
  P97     NUMBER
);


CREATE TABLE PCORNETSTUDIES.TBL_EXCL_CODES
(
  ICD9       VARCHAR2(25 BYTE),
  ICD10      VARCHAR2(25 BYTE),
  EXCLUSION  VARCHAR2(25 BYTE)
);


CREATE TABLE PCORNETSTUDIES.TBL_WHO_BMI
(
  SEX       VARCHAR2(30 BYTE),
  AGE_DAYS  NUMBER,
  L         NUMBER,
  M         NUMBER,
  S         NUMBER,
  P01       NUMBER,
  P1        NUMBER,
  P3        NUMBER,
  P5        NUMBER,
  P10       NUMBER,
  P15       NUMBER,
  P25       NUMBER,
  P50       NUMBER,
  P75       NUMBER,
  P85       NUMBER,
  P90       NUMBER,
  P95       NUMBER,
  P97       NUMBER,
  P99       NUMBER,
  P999      NUMBER
);


/*
CASES
* environment: PCORnet CDM 4.1, Oracle 12.2
    * PCORNETV41_201904_ETL = PCORnet schema
    * PCORNETSTUDIES = study-specific schema with select access on the PCORnet tables
* general approach
    * one CTE per documented step in the phenotype.  Each CTE step name corresponds to the phenotype step number.
* derivations from documented phenotype
    * added ICD-10 codes corresponding to the documented ICD-9 codes for exclusion
    * used the 97th percentile for BMI rather than the 99th percentile.
        * the CDC no longer releases the 99th percentile on charts.  
        * NIH recommends not using the 99th percentile due to sparse available data (https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4528342/)
*/

with step1 as
(select distinct
v.patid, d.birth_date, floor(v.measure_date - d.birth_date) as age_days,
floor(months_between(v.measure_date, d.birth_date)) as age_months,
case when d.sex = 'F' then 2 when d.sex = 'M' then 1 end as sex,
v.measure_date,
first_value(v.ht ignore nulls) over (partition by v.patid, v.measure_date order by v.measure_time asc) as ht, 
first_value(v.wt ignore nulls) over (partition by v.patid, v.measure_date order by v.measure_time asc) as wt 
from pcornetv41_201904_etl.vital v
join pcornetv41_201904_etl.demographic d on v.patid = d.patid
where v.encounterid is not null
and (v.ht is not null or v.wt is not null)),
step2 as
(select
step1.patid, step1.sex, step1.age_days, step1.age_months,
step1.ht, step1.wt,
(step1.wt/(step1.ht*step1.ht))*703 as bmi
from step1
left outer join pcornetstudies.tbl_cdc_lenage lenage on step1.sex = lenage.sex
    and step1.age_months = floor(lenage.agemos)
left outer join pcornetstudies.tbl_cdc_statureage statage on step1.sex = statage.sex
    and step1.age_months = floor(statage.agemos)
where step1.ht is not null and step1.wt is not null
and
(step1.ht > (lenage.p5 * 0.393701) --conversion from cm to inches
or 
step1.ht > (statage.p5 * 0.393701) --conversion from cm to inches
)),
step3 as
(select
step2.patid, step2.sex, step2.age_days, step2.age_months,
step2.ht, step2.wt, step2.bmi,
count(step2.age_days) over (partition by step2.patid) as bmi_count
from step2
where step2.age_days > 365 and step2.age_days < 2190),
step4 as 
(select
step3.patid, step3.sex, step3.age_days, step3.age_months,
step3.ht, step3.wt, step3.bmi
from step3
where step3.bmi_count >1),
step5 as
(select
step4.patid, step4.sex, step4.age_days, step4.age_months,
step4.ht, step4.wt, step4.bmi,
1 as bmi_all,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p97)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p97, cdcbmi.p97))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p97)) then 1 else 0 end as bmi_97,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p75)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p75, cdcbmi.p75))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p75)) then 1 else 0 end as bmi_75
from step4
left outer join pcornetstudies.tbl_who_bmi whobmi on step4.sex = whobmi.sex
    and step4.age_days = whobmi.age_days
    and step4.age_days >= 366
    and step4.age_days < 1860
left outer join pcornetstudies.tbl_cdc_bmi cdcbmi on step4.sex = cdcbmi.sex
    and step4.age_months = floor(cdcbmi.agemos)
    and step4.age_days >= 730),
step6a as
(select
step5.patid, step5.sex, step5.age_days, step5.age_months,
step5.ht, step5.wt, step5.bmi, step5.bmi_97, step5.bmi_75,
sum(step5.bmi_all) over (partition by step5.patid) as bmi_all_count,
sum(step5.bmi_97) over (partition by step5.patid) as bmi_97_count,
sum(step5.bmi_75) over (partition by step5.patid) as bmi_75_count
from step5),
step6b as
(select
step6a.patid, step6a.sex, step6a.age_days, step6a.age_months,
step6a.ht, step6a.wt, step6a.bmi, step6a.bmi_97, step6a.bmi_75,
step6a.bmi_all_count, step6a.bmi_97_count, step6a.bmi_75_count
from step6a
where bmi_97_count >1),
step7 as
(select
step6b.patid, step6b.sex, step6b.age_days, step6b.age_months,
step6b.ht, step6b.wt, step6b.bmi, step6b.bmi_97, step6b.bmi_75,
step6b.bmi_all_count, step6b.bmi_97_count, step6b.bmi_75_count
from step6b
where (step6b.bmi_75_count/step6b.bmi_all_count) > 0.5),
dx_ex as
(select
c.patid
from pcornetv41_201904_etl.condition c
left outer join pcornetstudies.tbl_excl_codes exc9 on c.condition = exc9.icd9  
    and c.condition_type = '09'
    and exc9.exclusion = 'CAEX'
left outer join pcornetstudies.tbl_excl_codes exc10 on c.condition = exc10.icd10
    and c.condition_type = '10'
    and exc10.exclusion = 'CAEX'
where exc9.icd9 is not null or exc10.icd10 is not null
union
select
d.patid
from pcornetv41_201904_etl.diagnosis d
left outer join pcornetstudies.tbl_excl_codes exc9 on d.dx = exc9.icd9  
    and d.dx_type = '09'
    and exc9.exclusion = 'CAEX'
left outer join pcornetstudies.tbl_excl_codes exc10 on d.dx = exc10.icd10
    and d.dx_type = '10'
    and exc10.exclusion = 'CAEX'
where exc9.icd9 is not null or exc10.icd10 is not null),
step8 as
(select
step7.patid, step7.sex, step7.age_days, step7.age_months,
step7.ht, step7.wt, step7.bmi, step7.bmi_97, step7.bmi_75,
step7.bmi_all_count, step7.bmi_97_count, step7.bmi_75_count
from step7
where not exists
    (select dx_ex.patid
    from dx_ex
    where dx_ex.patid = step7.patid))
--step 8 identified the final case population list
--now select the fields required for analysis
select
distinct
d.patid, 
d.birth_date,
floor(months_between(sysdate, d.birth_date)) as age_months,
d.sex, d.hispanic, d.race
from step8
join pcornetv41_201904_etl.demographic d on step8.patid = d.patid;


/*
CONTROL
* environment: PCORnet CDM 4.1, Oracle 12.2
    * PCORNETV41_201904_ETL = PCORnet schema
    * PCORNETSTUDIES = study-specific schema with select access on the PCORnet tables
* general approach
    * one CTE per documented step in the phenotype.  Each CTE step name corresponds to the phenotype step number.
* derivations from documented phenotype
    * added ICD-10 codes corresponding to the documented ICD-9 codes for exclusion
    * CDC does not release the 15th percentile.  Used nearest available instead
*/

with step1 as
(select distinct
v.patid, d.birth_date, floor(v.measure_date - d.birth_date) as age_days,
floor(months_between(v.measure_date, d.birth_date)) as age_months,
case when d.sex = 'F' then 2 when d.sex = 'M' then 1 end as sex,
v.measure_date,
first_value(v.ht ignore nulls) over (partition by v.patid, v.measure_date order by v.measure_time asc) as ht, 
first_value(v.wt ignore nulls) over (partition by v.patid, v.measure_date order by v.measure_time asc) as wt 
from pcornetv41_201904_etl.vital v
join pcornetv41_201904_etl.demographic d on v.patid = d.patid
where v.encounterid is not null
and (v.ht is not null or v.wt is not null)),
step2 as
(select
step1.patid, step1.sex, step1.age_days, step1.age_months,
step1.ht, step1.wt,
(step1.wt/(step1.ht*step1.ht))*703 as bmi
from step1
left outer join pcornetstudies.tbl_cdc_lenage lenage on step1.sex = lenage.sex
    and step1.age_months = floor(lenage.agemos)
left outer join pcornetstudies.tbl_cdc_statureage statage on step1.sex = statage.sex
    and step1.age_months = floor(statage.agemos)
where step1.ht is not null and step1.wt is not null
and
(step1.ht > (lenage.p5 * 0.393701) --conversion from cm to inches
or 
step1.ht > (statage.p5 * 0.393701) --conversion from cm to inches
)),
step3 as
(select
step2.patid, step2.sex, step2.age_days, step2.age_months,
step2.ht, step2.wt, step2.bmi,
count(step2.age_days) over (partition by step2.patid) as bmi_count
from step2
where 
--control step1 criteria
step2.age_days > 1459 and step2.age_days < 2191),
step4 as 
(select
step3.patid, step3.sex, step3.age_days, step3.age_months,
step3.ht, step3.wt, step3.bmi
from step3
where step3.bmi_count >1),
step5 as
(select
step4.patid, step4.sex, step4.age_days, step4.age_months,
step4.ht, step4.wt, step4.bmi,
1 as bmi_all,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p97)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p97, cdcbmi.p97))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p97)) then 1 else 0 end as bmi_97,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p95)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p95, cdcbmi.p95))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p95)) then 1 else 0 end as bmi_95,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p90)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p90, cdcbmi.p90))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p90)) then 1 else 0 end as bmi_90,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p85)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p85, cdcbmi.p85))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p85)) then 1 else 0 end as bmi_85,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p75)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p75, cdcbmi.p75))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p75)) then 1 else 0 end as bmi_75,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p50)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p50, cdcbmi.p50))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p50)) then 1 else 0 end as bmi_50,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p25)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p25, cdcbmi.p25))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p25)) then 1 else 0 end as bmi_25,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p10)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p10, cdcbmi.p10))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p10)) then 1 else 0 end as bmi_10,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p5)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p5, cdcbmi.p5))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p5)) then 1 else 0 end as bmi_5,
case when
    (--For ages 366-729 (days), use WHO
    (step4.age_days >= 365 and step4.age_days < 730 and step4.bmi >= whobmi.p3)
    or
    --For ages 730-1859, use greater of WHO or CDC
    (step4.age_days >= 730 and step4.age_days < 1860 and step4.bmi >= greatest(whobmi.p3, cdcbmi.p3))
    or
    --For ages 1860-2920, use CDC
    (step4.age_days >= 1860 and step4.age_days < 2921 and step4.bmi >= cdcbmi.p3)) then 1 else 0 end as bmi_3
from step4
left outer join pcornetstudies.tbl_who_bmi whobmi on step4.sex = whobmi.sex
    and step4.age_days = whobmi.age_days
    and step4.age_days >= 366
    and step4.age_days < 1860
left outer join pcornetstudies.tbl_cdc_bmi cdcbmi on step4.sex = cdcbmi.sex
    and step4.age_months = floor(cdcbmi.agemos)
    and step4.age_days >= 730),
--control step2
step6a as
(select
step5.patid, step5.sex, step5.age_days, step5.age_months,
step5.ht, step5.wt, step5.bmi, 
step5.bmi_97, step5.bmi_95, step5.bmi_90, step5.bmi_85, step5.bmi_75,
step5.bmi_50, step5.bmi_25, step5.bmi_10, step5.bmi_5, step5.bmi_3,
sum(step5.bmi_all) over (partition by step5.patid) as bmi_all_count,
sum(step5.bmi_97) over (partition by step5.patid) as bmi_97_count,
sum(step5.bmi_95) over (partition by step5.patid) as bmi_95_count,
sum(step5.bmi_90) over (partition by step5.patid) as bmi_90_count,
sum(step5.bmi_85) over (partition by step5.patid) as bmi_85_count,
sum(step5.bmi_75) over (partition by step5.patid) as bmi_75_count,
sum(step5.bmi_50) over (partition by step5.patid) as bmi_50_count,
sum(step5.bmi_25) over (partition by step5.patid) as bmi_25_count,
sum(step5.bmi_10) over (partition by step5.patid) as bmi_10_count,
sum(step5.bmi_5) over (partition by step5.patid) as bmi_5_count,
sum(step5.bmi_3) over (partition by step5.patid) as bmi_3_count
from step5),
step6b as
(select
step6a.patid, step6a.sex, step6a.age_days, step6a.age_months, 
step6a.ht, step6a.wt, step6a.bmi,
step6a.bmi_97, step6a.bmi_95, step6a.bmi_90, step6a.bmi_85,
step6a.bmi_75, step6a.bmi_50, step6a.bmi_25,
step6a.bmi_10, step6a.bmi_5, step6a.bmi_3,
step6a.bmi_all_count, step6a.bmi_97_count, step6a.bmi_95_count,
step6a.bmi_90_count, step6a.bmi_85_count, step6a.bmi_75_count,
step6a.bmi_50_count, step6a.bmi_25_count, step6a.bmi_10_count,
step6a.bmi_5_count, step6a.bmi_3_count
from step6a
where 
--all bmi measurements between 5th and 85th percentile
step6a.bmi_90_count = 0 --no measurements in 90th percentile or above
and step6a.bmi_5_count = step6a.bmi_all_count --all measurements in 5th percentile or above
),
dx_ex as
(select
c.patid
from pcornetv41_201904_etl.condition c
left outer join pcornetstudies.tbl_excl_codes exc9 on c.condition = exc9.icd9  
    and c.condition_type = '09'
    and exc9.exclusion = 'COEX'
left outer join pcornetstudies.tbl_excl_codes exc10 on c.condition = exc10.icd10
    and c.condition_type = '10'
    and exc10.exclusion = 'COEX'
where exc9.icd9 is not null or exc10.icd10 is not null
union
select
d.patid
from pcornetv41_201904_etl.diagnosis d
left outer join pcornetstudies.tbl_excl_codes exc9 on d.dx = exc9.icd9  
    and d.dx_type = '09'
    and exc9.exclusion = 'COEX'
left outer join pcornetstudies.tbl_excl_codes exc10 on d.dx = exc10.icd10
    and d.dx_type = '10'
    and exc10.exclusion = 'COEX'
where exc9.icd9 is not null or exc10.icd10 is not null),
--control step3
step7 as
(select
step6b.patid, step6b.sex, step6b.age_days, step6b.age_months, 
step6b.ht, step6b.wt, step6b.bmi,
step6b.bmi_97, step6b.bmi_95, step6b.bmi_90, step6b.bmi_85,
step6b.bmi_75, step6b.bmi_50, step6b.bmi_25,
step6b.bmi_10, step6b.bmi_5, step6b.bmi_3,
step6b.bmi_all_count, step6b.bmi_97_count, step6b.bmi_95_count,
step6b.bmi_90_count, step6b.bmi_85_count, step6b.bmi_75_count,
step6b.bmi_50_count, step6b.bmi_25_count, step6b.bmi_10_count,
step6b.bmi_5_count, step6b.bmi_3_count
from step6b
where not exists
    (select dx_ex.patid
    from dx_ex
    where step6b.patid = dx_ex.patid)),
step8 as
(select
step7.patid, step7.sex, step7.age_days, step7.age_months, 
step7.ht, step7.wt, step7.bmi,
step7.bmi_97, step7.bmi_95, step7.bmi_90, step7.bmi_85,
step7.bmi_75, step7.bmi_50, step7.bmi_25,
step7.bmi_10, step7.bmi_5, step7.bmi_3,
step7.bmi_all_count, step7.bmi_97_count, step7.bmi_95_count,
step7.bmi_90_count, step7.bmi_85_count, step7.bmi_75_count,
step7.bmi_50_count, step7.bmi_25_count, step7.bmi_10_count,
step7.bmi_5_count, step7.bmi_3_count,
case
--when all bmi percentiles <= p10, then control1
    when step7.bmi_25_count = 0 then 'control_1'
    else 'control_2'
end as control_group
from step7)
--step 8 identified the final case population list
--now select the fields required for analysis
select
distinct
d.patid,  step8.control_group,
d.birth_date,
floor(months_between(sysdate, d.birth_date)) as age_months,
d.sex, d.hispanic, d.race
from step8
join pcornetv41_201904_etl.demographic d on step8.patid = d.patid;