#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
