search

FINNGEN / ETL

second version for the ETL of the detailed longitudinal data to the OMOP-CDM
https://finngen.github.io/ETL/
MIT License
0 stars 0 forks source link

error in era tables #103

Closed shanmukhasampath closed 1 year ago

shanmukhasampath commented 1 year ago

There is an error in both era table SQLs where the end_date should be reverted back to drug_exposure_end_date or condition_end_date. However, the SQL adds extra 60 or 120 days to the respective end_date columns as shown below

Condition_Era https://github.com/FINNGEN/ETL/blob/b56df6a3ffbfb3da77c277f3fef60e6fd5543458/3_etl_code/ETL_Orchestration/sql/etl_condition_era.sql#L43

Drug_Era https://github.com/FINNGEN/ETL/blob/b56df6a3ffbfb3da77c277f3fef60e6fd5543458/3_etl_code/ETL_Orchestration/sql/etl_drug_era.sql#L50

For reference, this should be negative interval days from the official era SQL scripts https://github.com/OHDSI/ETL-CMS/blob/30dc50907993d65e376609ceb692a3e12921d88a/SQL/create_CDMv5_condition_era.sql#L53

It should look as below Condition_Era

DATE_ADD(event_date, INTERVAL -60 DAY) AS end_date

Drug_Era

DATE_ADD(event_date, INTERVAL -120 DAY) AS end_date
javier-gracia-tabuenca-tuni commented 1 year ago

wait, dotn fix this at the moment we shuld use the official script, the one we use i dont think is the official way here https://ohdsi.github.io/CommonDataModel/sqlScripts.html#Condition_Eras

shanmukhasampath commented 1 year ago

It is the same @javier-gracia-tabuenca-tuni . Except for the part I mentioned above in the issue.

javier-gracia-tabuenca-tuni commented 1 year ago

ok, then you can change that error