#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

Reply via email to