Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/ncdr_constants.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 4.0 KB
Line 
1/* HERON constants used in the construction of NCDR i2b2 concepts/facts
2
3Copyright (c) 2015 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6*/
7
8select * from blueherondata.concept_dimension where 1=0;
9
10create or replace view ncdr_audit_info as
11select * from blueherondata.source_master where source_cd like 'NCDR%';
12
13create or replace view ncdr_constants as
14  select
15    '\i2b2\NCDR' base_path, 'NCDR (DRAFT)' base_name, 'NCDR:' base_code,
16    'NCDR|CURRENT_AGE:' age_base_code
17  from dual
18;
19
20create or replace view ncdr_concepts as
21/* In the NCDR data file, each type of medication has its own column.  Then,
22each row has yes/no for each medication.  We want the user to be able to select
23the medications by name, so we generate medication concepts based on the original
24column name in the NCDR data file.
25*/
26with med_seqno as (
27  select '9500' seqno from dual -- Procedure Medications
28  union all
29  select '9505' seqno from dual -- Discharge Medications
30  ),
31/* We have some selections that are manually curated.  These are items
32specifically noted in the NCDR data dictionary but they weren't called
33"selections" like in most other sections of the documente.  Therefore, the script
34that parses the PDF and generates the concept hierarchy didn't catch them.  So,
35we copied them into a .csv and they are loaded as "curated data" into the
36ncdr_manual_selections table.
37
38See also #3224.
39*/
40manual_seqno as (
41  select distinct parent_seqno from ncdr_manual_selections -- Various manual selections
42  ),
43dict_concepts as (
44  select
45    dict.path,
46    /* The data dictionary doesn't always agree with what we find in the data.
47    See #3687.
48    */
49    case
50      when dict.name = 'No symptom, no angina' then 'No symptom; no angina'
51      else dict.name
52    end name,
53    to_char(dict.seqno) seqno, dict.va,
54    const.base_path || dict.path concept_path,
55    case
56      when dict.seqno is null then null
57      else const.base_code || dict.seqno ||
58        case
59          when dict.sel_no is not null then '.' || dict.sel_no
60          else ''
61        end
62      end concept_code,
63    case
64      when dict.seqno is not null and dict.va = 'LA'
65        and dict.sel_no is null
66      then null
67      else 1
68    end has_selections
69  from ncdrv4.ncdr_dictionary dict cross join ncdr_constants const
70  ),
71special_parents as (
72  select con.path, con.name, to_char(con.seqno) seqno, 'FA' va, con.concept_path, null concept_code, s.has_selections
73  from dict_concepts con
74  join (
75    select seqno, null has_selections from med_seqno
76    union all
77    select parent_seqno, 1 has_selections from manual_seqno
78    ) s
79  on con.seqno like s.seqno || '%'
80  ),
81med_children as (
82  select
83    par.*, substr(col.original_column_name, 1, instr(col.original_column_name, '[') - 1) chd_name,
84    substr(col.staged_column_name, length('seq_') + 1) chd_code
85  from special_parents par
86  join ncdrv4.ncdr_col_xlate col on col.staged_column_name like 'seq_' || par.seqno || '%'
87  join med_seqno med on med.seqno = par.seqno
88  ),
89manual_children as (
90  select
91    par.*, man.name chd_name, man.parent_seqno chd_code
92    from special_parents par
93    join ncdr_manual_selections man on man.parent_seqno like par.seqno || '%'
94  ),
95special_children as (
96  select path, name, seqno, va, path concept_path, concept_code, has_selections from (
97    select
98      path || rtrim(chd_name) || '\' path, chd_name name, chd_code seqno, 'LA' va,
99      const.base_code || chd_code concept_code, has_selections
100    from (
101      select * from med_children
102      union all
103      select * from manual_children
104      )
105    cross join ncdr_constants const
106    )
107  )
108select * from special_children
109union all
110select * from special_parents
111union all
112select * from dict_concepts dict
113where dict.seqno is null or dict.seqno not in (
114  select seqno from med_seqno
115  union all
116  select parent_seqno seqno from manual_seqno
117  )
118;
Note: See TracBrowser for help on using the repository browser.