#267: CDM V3 Vitals.Smoking History compliance -----------------------------------+---------------------------- Reporter: campbell | Owner: mprittie Type: design-issue | Status: reopened Priority: major | Milestone: data-domains3 Component: data-stds | Resolution: Keywords: Smoking history DM V3 | Blocked By: Blocking: 263, 486 | -----------------------------------+----------------------------
Comment (by mprittie): I created several intermediary tables, based on the SCHILS VITAL transformation, in order to debug the apparent issue with the tobacco type results. I know this is verbose, but I wanted to have a record of it. `TEMP_TOBACCO_L1`: {{{ create table TEMP_SMOKING_L1 as ( select obs.patient_num patid, obs.encounter_num encounterid, to_char(obs.start_Date,'YYYY-MM-DD') measure_date, to_char(obs.start_Date,'HH:MI') measure_time, nval_num, pcori_basecode, codes.pcori_code from i2b2fact obs inner join (select c_basecode concept_cd, c_fullname pcori_code, pcori_basecode from ( select '\PCORI\VITAL\BP\DIASTOLIC\' concept_path FROM DUAL union all select '\PCORI\VITAL\BP\SYSTOLIC\' concept_path FROM DUAL union all select '\PCORI\VITAL\HT\' concept_path FROM DUAL union all select '\PCORI\VITAL\WT\' concept_path FROM DUAL union all select '\PCORI\VITAL\ORIGINAL_BMI\' concept_path FROM DUAL union all select '\PCORI_MOD\BP_POSITION\' concept_path FROM DUAL union all select '\PCORI_MOD\VITAL_SOURCE\' concept_path FROM DUAL union all select '\PCORI\VITAL\TOBACCO\' concept_path FROM DUAL ) bp, pcornet_vital pm where pm.c_fullname like bp.concept_path || '%' ) codes on codes.concept_cd = obs.concept_cd ); }}} `TEMP_TOBACCO_L2`: {{{ create table TEMP_TOBACCO_L2 as ( select vit.patid, vit.encounterid, vit.measure_date, vit.measure_time , case when vit.pcori_code like '\PCORI\VITAL\HT%' then vit.nval_num else null end ht , case when vit.pcori_code like '\PCORI\VITAL\WT%' then vit.nval_num else null end wt , case when vit.pcori_code like '\PCORI\VITAL\BP\DIASTOLIC%' then vit.nval_num else null end diastolic , case when vit.pcori_code like '\PCORI\VITAL\BP\SYSTOLIC%' then vit.nval_num else null end systolic , case when vit.pcori_code like '\PCORI\VITAL\ORIGINAL_BMI%' then vit.nval_num else null end original_bmi , case when vit.pcori_code like '\PCORI_MOD\BP_POSITION\%' then SUBSTR(vit.pcori_code,LENGTH(vit.pcori_code)-2,2) else null end bp_position , case when vit.pcori_code like '\PCORI_MOD\VITAL_SOURCE\%' then SUBSTR(vit.pcori_code,LENGTH(vit.pcori_code)-2,2) else null end vital_source , case when vit.pcori_code like '\PCORI\VITAL\TOBACCO\02\%' then vit.pcori_basecode else null end tobacco , case when vit.pcori_code like '\PCORI\VITAL\TOBACCO\SMOKING\%' then vit.pcori_basecode else null end smoking , case when vit.pcori_code like '\PCORI\VITAL\TOBACCO\__\%' then vit.pcori_basecode else null end unk_tobacco , enc.admit_date from demographic pd left join TEMP_TOBACCO_L1 vit on vit.patid = pd.patid join encounter enc on enc.encounterid = vit.encounterid ); }}} `TEMP_TOBACCO_L3`: {{{ create table TEMP_SMOKING_L3 as ( select patid, encounterid, measure_date, measure_time, NVL(max(vital_source),'HC') vital_source, -- jgk: not in the spec, so I took it out admit_date, max(ht) ht, max(wt) wt, max(diastolic) diastolic, max(systolic) systolic, max(original_bmi) original_bmi, NVL(max(bp_position),'NI') bp_position, NVL(NVL(max(smoking),max(unk_tobacco)),'NI') smoking, NVL(NVL(max(tobacco),max(unk_tobacco)),'NI') tobacco from TEMP_SMOKING_L2 x where ht is not null or wt is not null or diastolic is not null or systolic is not null or original_bmi is not null or bp_position is not null or vital_source is not null or smoking is not null or tobacco is not null group by patid, encounterid, measure_date, measure_time, admit_date ); }}} -- Ticket URL: <http://informatics.gpcnetwork.org/trac/Project/ticket/267#comment:21> gpc-informatics <http://informatics.gpcnetwork.org/> Greater Plains Network - Informatics _______________________________________________ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev