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/validate_test_data.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: 1.4 KB
Line 
1/* Validate that the test data demonstrates certain edge cases.
2 *
3 * Copyright (c) 2015 University of Kansas Medical Center
4 * part of the HERON* open source codebase; see NOTICE file for license details.
5 * http://informatics.kumc.edu/work/wiki/HERON
6 */
7
8create or replace view validate_test_data_cnstnt as
9select 'test' as test_domain,
10       'validate_multi_pat_id_to_mrn' as multi_pat_id_to_mrn
11from dual;
12
13/* One edge case that is likely in production data, is the instance where
14 * multiple `pat_id`s align to the same MRN (possibly with leading zeroes) AND
15 * that MRN also corresponds to an IDX.
16 *
17 * The data we are testing for was likely added in:
18 * heron_staging/Clarity_data_load/multi_pat_id_to_mrn_align_idx_2385.sql
19 */
20create or replace view multi_pat_id_to_mrn_link_idx as
21(select *
22 from
23 (select ltrim(ii.identity_id, '0') MRN
24  from clarity.patient p
25  join clarity.identity_id ii
26    on p.pat_id = ii.pat_id
27   and ii.identity_type_id = 10
28  group by ltrim(ii.identity_id, '0')
29  having count(*) > 1
30  ) dbl_mrn
31 join kupi.idx_table idx
32   on dbl_mrn.mrn = ltrim(idx.hospital_medical_record, '0')
33);
34
35insert into etl_test_values (test_value,
36                             test_domain, test_name,
37                             result_id, result_date)
38select (select count(*) from multi_pat_id_to_mrn_link_idx) as test_value,
39       test_domain, multi_pat_id_to_mrn as test_name,
40       sq_result_id.nextval, sysdate
41from validate_test_data_cnstnt;
42commit;
Note: See TracBrowser for help on using the repository browser.