#186: Querying age by numerical constraints
-----------------------------------------+----------------------------
Reporter: huhickman | Owner: huhickman
Type: problem | Status: reopened
Priority: major | Milestone: data-domains2
Component: data-stds | Resolution:
Keywords: age valueset obesity-cohort | Blocked By:
Blocking: 67 |
-----------------------------------------+----------------------------
Comment (by huhickman):
The SQL to create the prototype fact table is:
{{{
#!sql
create or replace view obs_fact_age_days as
select 'fabricated_for_' || p.pat_id as encounter_ide,
p.patient_ide,
BIRTH_DATE as start_date,
'LOINC:30525-0' as concept_cd,
'@' as modifier_cd,
case when death_date is null then cast(null as date) else
death_date end as end_DATE,
'@' as provider_id,
'N' as valtype_cd,
'E' as TVAL_CHAR,
round(coalesce(death_date, sysdate) - birth_date + date_shift ) as
NVAL_NUM,
cast(NULL as varchar2(50)) as VALUEFLAG_CD,
cast(NULL as decimal(18,5)) as QUANTITY_NUM,
cast(null as VARCHAR2(50)) as LOCATION_CD,
cast(NULL as varchar2(50)) as OBSERVATION_BLOB,
cast(NULL as decimal(18,5)) as CONFIDENCE_NUM,
cast(NULL as date) as UPDATE_DATE,
sysdate as DOWNLOAD_DATE,
sysdate as IMPORT_DATE,
'e...@nebraskmed.com' as SOURCESYSTEM_CD,
'DAYS' as UNITS_CD,
1 as instance_num,
100 as upload_id
from patient_dimension p
where birth_date is not null
}}}
In our case, this is called from a stored procedure.
A second stored procedure inserts these facts into the de-identified fact
table in our i2b2 instance (adjust dates, substitutes encounter_num and
patient_num, etc.).
Note that `nval_num` field is date shifted in this view. Additionally,
these facts should be refreshed daily to maintain accuracy.
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/186#comment:5>
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
