source: heron_load/epic_med_mapping.sql

Last change on this file was 0:01b16d588a29, checked in by Nathan Graham <ngraham@…>, 3 months ago

Merge with clarity_2014_3207

File size: 23.5 KB
Line 
1/* epic_med_mapping -- Map Epic meds to standard terminology RxNorm/NDF-RT
2
3Copyright (c) 2014 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
7Hierarchy based on "Drug Products by VA Class".  This was the organization
8suggested by "2010 paper on mashing up RxNorm and NDF-RT hierarchy" found here:
9  http://www.ncbi.nlm.nih.gov/pubmed/21347044
10
11The RxNorm documentation is located here:
12http://www.nlm.nih.gov/research/umls/rxnorm/docs/2012/rxnorm_doco_full_2012-3.html
13
14Desired hierarchy:
15
16VA Drug Class (NDF-RT)
17  |- Semantic Clinical Drug and Form (SCDF)/Semantic Branded Drug and Form (SBDF) from RxNorm
18    |- Clarity Drug
19
20Notes about manual curation:
21med_map_manual_curation is a table that maps a clarity medication ID to a parent
22concept.  It is designed to be used for medications that we couldn't map to RxNORM
23directly from information in Clarity (via GCN, NDC, etc). This parent could be a
24VA class or an SCDF/SBDF.
25
26The table has the following columns:
27CLARITY_MEDICATION_ID - the id of the med without direct linkage to RxNORM
28CLARITY_NAME (not used for mapping - just for eyeballing)
29FACTS (not used for mapping - just for eyeballing)
30PATIENTS (not used for mapping - just for eyeballing)
31VA_NAME (not used for mapping - just for eyeballing)
32VA_RXAUI - If we didn't map to an SDF AUI (below), the fall back on this one for VA class.
33SDF_RXAUI - Map here if not null, otherwise fall back on the VA_RXAUI (above)
34SDF_NAME (not used for mapping - just for eyeballing)
35
36Note that this code uses RxNORM relationships even if the concept is "suppressed". 
37
38From:
39http://www.nlm.nih.gov/research/umls/rxnorm/docs/2014/rxnorm_doco_full_2014-3.html#conso
40   
41    "Suppressible flag. Values = N, O, Y, or E.
42    N - not suppressible.
43    O - Specific individual names (atoms) set as Obsolete because the name is no
44         longer provided by the original source.
45    Y - Suppressed by RxNorm editor.
46    E - unquantified, non-prescribable drug with related quantified, prescribable
47        drugs. NLM strongly recommends that users not alter editor-assigned
48        suppressibility."
49
50TODOs:
51 - Consider MedEx NLP for meds that didn't match to an RxCUI.
52 - Consider medications that might map to a VA class directly but not an SCDF/SBDF.
53*/
54
55
56-- Check access: should be running on nheron
57select upload_id from NIGHTHERONDATA.observation_fact where 1=0;
58
59-- Check access to KUMC/rxnorm
60select rxcui from rxnorm.rxnconso where 1=0;
61
62-- Audit constants for stuff from RxNorm
63create or replace view rxnorm_audit_info as
64select * from BlueHeronData.source_master@deid
65where source_cd like 'rxnorm@%';
66
67/* Map our clarity medication id to GCN and then to RxCUI
68*/
69create or replace view clarity_med_id_to_rxcui_gcn as select * from(
70  with med_id_to_gcn as(
71    /* Map the clarity medication ID to the clarity GCN.  Note that there may be
72    multiple GCNs for a single med id which means it may end up in more than one
73    place in the hierarchy.
74    */
75    select
76      ccm.medication_id, crmg.gcn_seqno, ccm.name
77    from clarity.clarity_medication ccm
78    join clarity.rx_med_gcnseqno crmg on crmg.medication_id = ccm.medication_id
79    ),
80  gcn_to_rxui as(
81    --Map the clarity GCN to the RxNorm GCN/CUI
82    select
83      mtog.medication_id, mtog.gcn_seqno, mtog.name clarity_name, rxc.code,
84      rxc.rxcui, rxc.tty, rxc.str rxname
85    from med_id_to_gcn mtog
86    join rxnorm.rxnconso rxc on rxc.code = lpad(mtog.gcn_seqno, 6, '0')
87    where rxc.sab='NDDF' and rxc.tty != 'IN' --skip ingredients (#1246 comment:13)
88    )
89  select
90    medication_id clarity_med_id, gcn_seqno clarity_gcn, code rxn_gcn, rxcui, tty,
91    clarity_name, rxname
92  from gcn_to_rxui
93  );
94
95
96/* Map our clarity medication id to NDC and then to an RxCUI
97*/
98create or replace view clarity_med_id_to_rxcui_ndc as select * from(
99  with med_id_to_ndc as(
100    /* Map the clarity medication ID to the clarity NDC.  Note that there may be
101    multiple NDCs for a single med id which means it may end up in more than one
102    place in the hierarchy.
103   
104    Note that we don't currently handle NDC normalization.  See:
105    http://www.nlm.nih.gov/research/umls/rxnorm/NDC_Normalization_Code.rtf
106    */
107    select
108      ccm.medication_id, cndc.ndc_code, ccm.name clarity_name
109    from clarity.clarity_medication ccm
110    join clarity.clarity_ndc_codes cndc on cndc.medication_id=ccm.medication_id
111    ),
112  ndc_to_rxui as(
113    --Map the clarity NDC to the RxNorm NDC/CUI
114    select
115      mton.medication_id, mton.ndc_code, sat.rxcui, con.str rxname, clarity_name
116    from med_id_to_ndc mton
117    join rxnorm.rxnsat sat on sat.atv = mton.ndc_code
118    join rxnorm.rxnconso con on sat.rxcui = con.rxcui
119    where sat.atn='NDC' --and con.tty != 'IN' --skip ingredients (#1246 comment:13)
120    )
121  select
122    medication_id clarity_med_id, ndc_code, clarity_name, rxcui, rxname
123  from ndc_to_rxui
124  ); 
125
126
127/* Map Clarity medications to RxNorm (Clarity medication_id to rxcui).
128Temporary tables are used instead of views to reduce run time.
129*/
130whenever sqlerror continue;
131drop table clarity_med_id_to_rxcui;
132whenever sqlerror exit;
133
134create table clarity_med_id_to_rxcui as(
135  select distinct
136    clarity_med_id, rxcui, clarity_name, 'GCN' as src
137  from
138    clarity_med_id_to_rxcui_gcn
139
140  union
141
142  select distinct
143    clarity_med_id, rxcui, clarity_name, 'NDC' as src
144  from
145    clarity_med_id_to_rxcui_ndc
146  );
147
148commit;
149
150
151/* Constants used later in the query for creating concept codes
152*/
153create or replace view med_constants as
154  select
155    'RXCUI:' rxcui_prefix,
156    'RXAUI:' rxaui_prefix,
157    'MEDICATION_ID:' local_med_id_prefix,
158    'KUH|MEDICATION_ID:' med_concept_prefix,
159    'LA' leaf,
160    'FA' folder,
161    3149154 va_parent_aui, -- "Drug Products by VA Class" (RxAUI = 3149154)
162    'Medications' meds_top_level,
163    'Other Medication Concepts' other_meds_bucket
164  from dual
165;
166
167/* Parent/child relationships for the VA class hierarchy
168*/
169create or replace view va_classes as (
170  select * from (
171    select
172      con1.str va_parent_name, con1.rxcui va_parent_rxcui, con1.rxaui va_parent_rxaui,
173      rel.rel,
174      con2.str va_child_name, con2.rxcui va_child_rxcui, con2.rxaui va_child_rxaui, c.folder visualattributes
175    from rxnorm.rxnrel rel
176    join rxnorm.rxnconso con1 on con1.rxaui=rel.rxaui1
177    join rxnorm.rxnconso con2 on con2.rxaui=rel.rxaui2
178    join rxnorm.rxnsat sat2 on sat2.code = con2.scui, med_constants c
179    where rel.sab = 'NDFRT' and rel.rel = 'CHD' and sat2.atv = 'VA Class'
180     
181    union all
182     
183    select
184      null va_parent_name, null va_parent_rxcui, null va_parent_rxaui,
185      'CHD' rel,
186      con.str va_child_name, con.rxcui va_child_rxcui, con.rxaui va_child_rxaui, c.folder visualattributes
187    from rxnorm.rxnconso con, med_constants c
188    where con.rxaui = c.va_parent_aui
189    )
190  where va_child_rxaui not in (
191    /* Skip "[AA000] INTRODUCTION" as it doesn't seem relevant for our medication
192    hierarchy.
193    */
194    3257489
195    )
196  )
197;
198
199
200/* Find all SCDF/SBDF concepts from RxNorm
201*/
202whenever sqlerror continue;
203truncate table sdf_children;
204drop table sdf_children;
205whenever sqlerror exit;
206
207create table sdf_children as (
208  select * from ( -- extra select because Oracle says "SQL Error: ORA-32034: unsupported use of WITH clause" otherwise
209    with scdf_sbdf as (
210      select
211        con1.str sdf_name, con1.rxcui sdf_rxcui, con1.rxaui sdf_rxaui, con1.sab sdf_sab,
212        con2.str isa_name, con2.rxcui isa_rxcui, con2.rxaui isa_rxaui, con2.sab isa_sab
213      /* None of the concepts marked as SCDF are direct children of any of the VA classes.  So, utilze the 'isa'
214      relationship.
215      */
216      from rxnorm.rxnrel rel
217      join rxnorm.rxnconso con1 on con1.rxcui=rel.rxcui1
218      join rxnorm.rxnconso con2 on con2.rxcui=rel.rxcui2
219      where rel.rela = 'isa' and (con1.tty = 'SCDF' or con1.tty = 'SBDF')
220      )
221    /* Parent/child relationships for SCDF/SBDF to VA class
222    */
223    select distinct --Lots of things match the 'isa' criteria - just need the distinct SCDF/SBDF
224      va.va_child_name va_name, va.va_child_rxcui va_rxcui, va.va_child_rxaui va_rxaui,
225      rel.rel,
226      sdf.sdf_name, sdf.sdf_rxcui, sdf.sdf_rxaui, c.folder visualattributes
227    from rxnorm.rxnrel rel
228    join va_classes va on va.va_child_rxaui=rel.rxaui1
229    join scdf_sbdf sdf on sdf.isa_rxaui = rel.rxaui2 and rel.rel = 'CHD', med_constants c
230    -- where sdf.sdf_sab = 'RXNORM' --Note, all SCDF/SBDF that join on rxaui are from RXNORM
231  )
232); 
233 
234 
235/* Distinct mapping of clarity mediation id to SCDF/SBDF
236*/
237whenever sqlerror continue;
238truncate table clarity_children;
239drop table clarity_children;
240whenever sqlerror exit;
241
242create table clarity_children as (
243  select * from (
244    with
245    clarity_matches as (
246      select distinct -- Many RxCUI matches for a single medication - we just need one
247        con1.rxaui sdf_aui, cmed.clarity_med_id
248      from rxnorm.rxnrel rel
249      join rxnorm.rxnconso con1 on con1.rxcui=rel.rxcui1
250      join rxnorm.rxnconso con2 on con2.rxcui=rel.rxcui2
251      join sdf_children sdf on sdf.sdf_rxaui = con1.rxaui
252      join clarity_med_id_to_rxcui cmed on cmed.rxcui = rel.rxcui2
253      where rel.rela = 'isa'
254      )
255    /* Parent/child relationships for clarity medication id to SCDF/SBDF
256    */
257    select
258      con.str sdf_name, con.rxcui sdf_rxcui, con.rxaui sdf_rxaui,
259      cmed.name clarity_name, cmed.medication_id clarity_med_id, c.leaf visualattributes
260    from clarity_matches cmatch
261    join rxnorm.rxnconso con on con.rxaui = cmatch.sdf_aui
262    join clarity.clarity_medication cmed on cmed.medication_id = cmatch.clarity_med_id, med_constants c
263  )
264);
265
266/* Create i2b2-style terms
267*/
268whenever sqlerror continue;
269truncate table rxnorm_terms;
270drop table rxnorm_terms;
271whenever sqlerror exit;
272
273
274create table rxnorm_terms as
275select
276  lvl c_hlevel, concept_path c_fullname, child_name as c_name,
277  visualattributes c_visualattributes, child_concept c_basecode,
278  concept_path c_dimcode, sysdate update_date, sysdate download_date,
279  sysdate import_date, aud.source_cd sourcesystem_cd, norm.*
280from (
281  select level + 1 as lvl,
282  '\i2b2\' || mc.meds_top_level || SYS_CONNECT_BY_PATH(child_uid, '\') || '\' as concept_path,
283  parent_child.*
284  from med_constants mc, (
285    /* VA Class parent/child */
286    select
287      va_parent_name parent_name, rxaui_prefix || va_parent_rxaui parent_uid, 
288      rxcui_prefix || va_parent_rxcui parent_concept,
289      va_child_name child_name, rxaui_prefix || va_child_rxaui child_uid,
290      rxcui_prefix || va_child_rxcui child_concept, visualattributes
291    from va_classes, med_constants
292    union all
293    /* SCDF/SBDF to VA Class */
294    select
295      va_name parent_name, rxaui_prefix || va_rxaui parent_uid,
296      rxcui_prefix || va_rxcui parent_concept,
297      sdf_name child_name, rxaui_prefix || sdf_rxaui child_uid,
298      rxcui_prefix || sdf_rxcui child_concept, visualattributes
299    from sdf_children, med_constants
300    union all
301    /* Clarity medications to SCDF/SBDF */
302    select
303      sdf_name parent_name, rxaui_prefix || sdf_rxaui parent_uid,
304      rxcui_prefix || sdf_rxcui parent_concept,
305      clarity_name child_name, local_med_id_prefix || to_char(clarity_med_id) child_uid,
306      med_concept_prefix || clarity_med_id child_concept,
307      visualattributes
308    from clarity_children, med_constants
309    union all
310    /* Manual curation */
311    select
312      con.str parent_name, rxaui_prefix || con.rxaui parent_uid,
313      rxcui_prefix || con.rxcui parent_concept,
314      cm.name child_name, local_med_id_prefix || to_char(clarity_medication_id) child_uid,
315      med_concept_prefix || cm.medication_id child_concept,
316      'LA ' visualattributes
317    from med_constants, (
318      select distinct --duplications in curated data?
319        clarity_medication_id, coalesce(sdf_rxaui, va_rxaui) parent_rxaui
320      from med_map_manual_curation@deid
321      where clarity_medication_id is not null
322      )
323    join clarity.clarity_medication cm on cm.medication_id = clarity_medication_id
324    join rxnorm.rxnconso con on con.rxaui = parent_rxaui
325    union all
326    /* Bucket for "other" medications.
327    TODO: Consider organizing these "other" medications into smaller buckets.
328    For example, have alphabetical folders based on medication name - something
329    like "A-D", "E-H", etc.
330    */
331    select
332      con.str parent_name, rxaui_prefix || con.rxaui parent_uid,
333      mc.rxcui_prefix || con.rxcui parent_concept,
334      mc.other_meds_bucket child_name, mc.other_meds_bucket child_uid,
335      null child_concept,
336      mc.folder
337    from med_constants mc, rxnorm.rxnconso con
338    where con.rxaui = mc.va_parent_aui       
339  ) parent_child
340  start with parent_child.parent_uid = rxaui_prefix || mc.va_parent_aui
341  connect by prior parent_child.child_uid = parent_child.parent_uid
342  ), rxnorm_audit_info aud, BlueHeronMetadata.normal_concept@deid norm
343;
344
345commit;
346
347/* Make sure that medications we mapped to the RxNorm ontology cover nearly all
348of the medication facts we have. 
349
350Note that in previous versions of the code this test came _after_ we included
351the top 500 medications by fact count in the "Other Medications" folder.  So,
352previously things in the "Other Medications" folder were considered mapped to
353RxNorm.
354*/
355insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
356with test_key as (
357    select 'Medications' test_domain,
358    'most_meds_mapped' test_name from dual
359  )
360, med_counts as (
361    select substr(concept_cd, length(mc.med_concept_prefix)+1) med_id, facts, patients
362    from blueheronmetadata.counts_by_concept@deid, med_constants mc
363    where concept_cd like mc.med_concept_prefix || '%'
364  )
365, mapped_meds as (
366    select distinct substr(c_basecode, length(mc.med_concept_prefix)+1) med_id
367    from rxnorm_terms, med_constants mc
368    where c_basecode like mc.med_concept_prefix || '%'
369  )
370, mapped_counts as (
371    select * from med_counts mc
372    join mapped_meds mm on mm.med_id = mc.med_id
373  )
374, total_fact_count as (
375    select sum(facts) cnt from med_counts
376  )
377, mapped_fact_count as (
378    select sum(facts) cnt from mapped_counts
379  )
380, perc_meds_mapped as (
381    select round((mp.cnt / tf.cnt) * 100) test_value
382    from mapped_fact_count mp, total_fact_count tf
383  )
384  select test_value, test_key.*, sq_result_id.nextval, sysdate
385  from test_key, perc_meds_mapped;
386commit;
387
388
389/* Test to make sure that KUH|MEDICATION_ID:210319 = "CEFAZOLIN INJ 1GM IVP" is
390in the right place in the hierarchy (see ticket 1451).
391"CEFAZOLIN INJ 1GM IVP" in clarity is a "Cefazolin Injectable Solution"
392*/
393select case when count(*) = 0 then 1/0 else 1 end med_match_by_name_1451 from (
394  select c_name clarity_name, str rxnorm_sdf from (
395    /* Get the parent RXAUI from the fullname - like RXAUI:1576284 from
396    \i2b2\Medications\RXAUI:3257505\RXAUI:3257510\RXAUI:3257511\RXAUI:1576284\MEDICATION_ID:210319\
397    */
398    select
399      trim('\' from regexp_substr(c_fullname,'\\[^\\]+\\', instr(c_fullname, '\', -1, 3))) sdf_rxaui,
400      c_name
401    from rxnorm_terms
402    where c_basecode = 'KUH|MEDICATION_ID:210319'
403    ) mp
404  -- Join with RxNorm for eyeballing
405  join rxnorm.rxnconso rxc on (select rxaui_prefix from med_constants) || rxc.rxaui = mp.sdf_rxaui
406  where rxc.rxaui = 1576284 --Cefazolin Injectable Solution
407  );
408
409
410create or replace view mapped_meds as
411select
412  distinct substr(rxt.c_basecode, length(mc.med_concept_prefix)+1) med_id
413from rxnorm_terms rxt, med_constants mc
414where rxt.c_basecode like mc.med_concept_prefix || '%';
415 
416create or replace view unmapped_meds as
417with
418mpl as (
419  --Ugh, I couldn't use the view directly in a length() function directly for some reason
420  select(length((select med_concept_prefix from med_constants))) l from dual
421  ),
422med_counts as (
423  select substr(cbc.concept_cd, mpl.l + 1) clarity_med_id, cbc.facts, cbc.patients
424    from mpl, med_constants mc, blueheronmetadata.counts_by_concept@deid cbc
425    where cbc.concept_cd like mc.med_concept_prefix || '%'
426  )
427select cmed.medication_id clarity_medication_id, cmed.name clarity_name, mc.facts, mc.patients
428from med_counts mc
429left join mapped_meds mm on mm.med_id = mc.clarity_med_id 
430join clarity.clarity_medication cmed on to_char(cmed.medication_id) = mc.clarity_med_id
431where mm.med_id is null;
432
433
434/* Put the top 500 unmapped medications (by fact count) in the "other" bucket
435The query construction might seem odd, but writing it this way increased
436performance dramatically.
437*/
438insert into rxnorm_terms
439with
440other_meds as (
441  select * from
442    rxnorm_terms rt, med_constants mc, unmapped_meds um
443  where rt.c_name = mc.other_meds_bucket
444  )
445select
446  c_hlevel + 1 c_hlevel, c_fullname || clarity_medication_id || '\' c_fullname,
447  clarity_name c_name, leaf c_visualattributes,
448  med_concept_prefix || clarity_medication_id c_basecode,
449  c_fullname || clarity_medication_id || '\' c_dimcode, update_date,
450  download_date, import_date, sourcesystem_cd, norm.*
451from BlueHeronMetadata.normal_concept@deid norm, (
452  select * from other_meds
453  order by facts desc
454  )
455where rownum < 500
456;
457
458commit;
459
460select case when count(*) = 0 then 1 else 1/0 end ends_with_slash from (
461  select * from rxnorm_terms where substr(c_fullname, length(c_fullname)) != '\'
462  );
463
464select case when cnt = 0 then 1 else 1/0 end no_null_names from (
465  select count(*) cnt from rxnorm_terms where c_name is null
466  );
467
468
469/* For some reason, inserting over a link kept giving me "column ambiguously
470defined".  But, if I create a table first and then insert over the link, it
471worked.
472*/
473
474delete from blueheronmetadata.rxnorm_terms@deid;
475insert into blueheronmetadata.rxnorm_terms@deid (
476  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
477  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype, c_operator, c_dimcode,
478  c_tooltip, m_applied_path, update_date, download_date, import_date, sourcesystem_cd,
479  m_exclusion_cd
480  )
481select
482  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
483  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
484  c_operator, c_dimcode, null c_tooltip, m_applied_path, update_date, download_date,
485  import_date, sourcesystem_cd, null m_exclusion_cd
486from rxnorm_terms
487
488union all
489
490/* Exclude some medications from having the modifiers applied.
491*/
492select
493  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
494  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
495  c_operator, c_dimcode, c_tooltip, m.m_applied_path || '%' m_applied_path,
496  update_date, download_date, import_date, sourcesystem_cd, 'X' m_exclusion_cd
497from (
498  select c_fullname m_applied_path
499  from med_map_exclude_modifiers@deid ex
500  join rxnorm_terms rxt on rxt.c_basecode = ex.concept_cd
501  ) m, blueheronmetadata.epic_terms@deid
502where c_tablename = 'MODIFIER_DIMENSION'
503and c_fullname like '\Medication\%';
504
505
506/* Make sure there are no duplicates in the full paths of the RxNorm terms table.
507Note that a particular term can be excluded from having a modifier attached even
508when that modifier is applied at a higher level in the hierarchy.  To exclude a
509term, insert another modifier row into the terms table with the same full path
510but with applied path set to the term to be excluded and the exclusion code set
511to 'X'.  So, duplicate rows are acceptable if they are used in this way to
512exclude terms from modifiers.
513*/
514select case when count(*) > 0 then 1/0 else 1 end dup_concepts from(
515  select count(*), c_fullname, m_exclusion_cd, m_applied_path
516  from blueheronmetadata.rxnorm_terms@deid
517  group by c_fullname , m_exclusion_cd, m_applied_path
518  having count(*) > 1 order by count(*) desc
519);
520
521/* To help generate a curated data file of medications that didn't map to RxNorm,
522use this query to map medications with  similar names.  Note that first load
523/curated_data/med_map_like_name.csv which is a manually curated list of medication
524names with wild cards like (%CEFEPIME%).
525*/
526/*
527with
528like_to_va_name as (
529  select distinct sdfc.va_name, sdfc.va_rxaui, sdfc.sdf_rxaui, sdfc.sdf_name, ln.like_name
530  from clarity_children cc
531  join med_map_like_name@deid ln on upper(cc.clarity_name) like ln.like_name
532  join sdf_children sdfc on sdfc.sdf_rxaui = cc.sdf_rxaui
533  order by sdfc.va_name
534  )
535select
536  um.clarity_medication_id, um.clarity_name, um.facts, um.patients,
537  lva.va_name, lva.va_rxaui, lva.sdf_rxaui, lva.sdf_name
538from unmapped_meds um
539join like_to_va_name lva on um.clarity_name like lva.like_name
540order by facts desc --for eyeballing highest fact counts first
541;
542*/
543
544/* Suggested mappings for unmapped meds based on mapped meds with the same
545therapeutic and pharmaceutical classes according to clarity */
546/*
547with
548unmapped_classes as (
549  select um.*, cmed.thera_class_c, cmed.pharm_class_c, cmed.pharm_subclass_c
550  from unmapped_meds um
551  join clarity.clarity_medication cmed on cmed.medication_id = um.clarity_medication_id
552  ),
553mapped_classes as (
554  select mm.*, cmed.name, cmed.thera_class_c, cmed.pharm_class_c, cmed.pharm_subclass_c
555  from mapped_meds mm
556  join clarity.clarity_medication cmed on cmed.medication_id = mm.med_id
557  ),
558same_classes as (
559  select
560    min(mc.med_id) mapped_id,
561    uc.clarity_medication_id unmapped_med_id, uc.clarity_name unmapped_name,
562    uc.facts, uc.patients, thr.name thera_class, phr.name pharm_class,
563    phrs.name pharm_subclass
564  from unmapped_classes uc
565  join mapped_classes mc on mc.thera_class_c = uc.thera_class_c
566    and mc.pharm_class_c = uc.pharm_class_c
567    and mc.pharm_subclass_c = uc.pharm_subclass_c
568  join clarity.zc_thera_class thr on thr.thera_class_c = mc.thera_class_c
569  join clarity.zc_pharm_class phr on phr.pharm_class_c = mc.pharm_class_c
570  join clarity.zc_pharm_subclass phrs on phrs.pharm_subclass_c = mc.pharm_subclass_c
571  group by
572    uc.clarity_medication_id, uc.clarity_name, uc.facts, uc.patients, thr.name,
573    phr.name, phrs.name
574  )
575select
576  unmapped_med_id clarity_medication_id, unmapped_name clarity_name, facts,
577  patients, suggested_va va_name, suggested_va_aui va_rxaui, null sdf_rxaui,
578  null sdf_name
579from (
580  select -- Subquery good for eyeballing
581    cmed.name mapped_name, sc.*, sdfc.va_name suggested_va, sdfc.va_rxaui suggested_va_aui,
582    sdfc.sdf_name suggested_sdf, sdfc.sdf_rxaui suggested_sdf_aui
583  from same_classes sc
584  join clarity_children cc on cc.clarity_med_id = sc.mapped_id
585  join sdf_children sdfc on sdfc.sdf_rxaui = cc.sdf_rxaui
586  join clarity.clarity_medication cmed on cmed.medication_id = sc.mapped_id
587  order by facts desc
588  )
589
590
591select rxt.*, cbc.facts, cbc.patients from rxnorm_terms rxt
592left join blueheronmetadata.counts_by_concept@deid cbc on cbc.concept_cd = rxt.c_basecode
593
594union all
595
596select
597  c_hlevel, c_fullname, c_name, c_visualattributes, c_basecode,
598  c_dimcode, sysdate update_date, sysdate download_date,
599  sysdate import_date, sourcesystem_cd, norm.*, 0 facts, 0 patients
600from I2B2METADATA2.I2B2@deid m, BlueHeronMetadata.normal_concept@deid norm
601where m.c_name = 'Medications'
602;
603*/
604 
605/* Just a general helper to show unmapped meds by fact count.
606*/
607-- select * from unmapped_meds order by facts desc;
608
609--Drop temporary tables.
610drop table clarity_med_id_to_rxcui;
611drop table rxnorm_terms;
612drop table clarity_children;
613drop table sdf_children;
Note: See TracBrowser for help on using the repository browser.