source: heron_load/test_uhc.sql

/** test_uhc.sql -- check i2b2 ETL results vs source data.*/

Copyright (c) 2012 University of Kansas Medical Center
part of the HERON* open source codebase; see NOTICE file for license details.
* http://informatics.kumc.edu/work/wiki/HERON

--Make sure we are in deid
select concept_cd from BlueHeronData.observation_fact where 1 = 0;

--Make sure we can talk to epic
select pat_id from clarity.patient@id where 1 = 0;

-- Check that at least some data is loaded.
select case when fact_uploads > 0 then 1
            else 1/0
      end as some_data_loaded from (
select count(*) fact_uploads from BlueHeronData.upload_status where load_status = 'OK'
);

/** Create mrn_patient_mapping
TODO: Ask Dan.  Is this acceptable?  Not sure how best to create this mapping 
for the test.
*/
create or replace view mrn_patient_mappings as
select patient_ide as MRN, patient_num, patient_ide_source
from NightHeronData.patient_mapping@nheron pmap
where pmap.patient_ide_status = 'A' and pmap.patient_ide_source like 'SMS@%';


/*******************************************************************************
Diagnosis->MDC (Major Diagnostic Category)
*******************************************************************************/
select case when source_num=i2b2_num then 1 else 1/0 end as uhc_diag_cat_match_source from(
  with 
  i2b2_query as(
    select count(*) i2b2_num from(
    /* 
    The following is the query generated by i2b2 (blueherondata.qt_query_master)
    */
      SELECT
          patient_num
      FROM BlueHerondata.observation_fact
      WHERE concept_cd IN
        (SELECT concept_cd
        FROM BlueHerondata.concept_dimension
        WHERE concept_path LIKE '\i2b2\UHC\Diagnosis\Major Diagnostic Category\%'
        )
      GROUP BY patient_num
      HAVING COUNT(*) >= 1
    )
  ),
  source_query as (
    select count(*) source_num from(
      select 
        uenc.patientid
      from 
        uhc.encounter uenc
      join mrn_patient_mappings pmap
      on uenc.patientid=pmap.mrn
      where uenc.msdrgmdc is not null
    )
  )
  select
    source_num, i2b2_num
  from source_query, i2b2_query);


/*******************************************************************************
Demographics->Race
*******************************************************************************/
select case when source_num=i2b2_num then 1 else 1/0 end as uhc_race_match_source from(
  with i2b2_query as(
    select count(*) i2b2_num from(
      /* 
      The following is the query generated by i2b2 (blueherondata.qt_query_master)
      */
      SELECT
        patient_num
      FROM BlueHerondata.observation_fact
      WHERE concept_cd IN
        (SELECT concept_cd
        FROM BlueHerondata.concept_dimension
        WHERE concept_path LIKE '\i2b2\UHC\Demographics\Race\%'
        )
      GROUP BY patient_num
      HAVING COUNT(*) >= 1
    )
  ),
  source_query as(
    select count(*) source_num from(
      select
        uenc.patientid
      from
        uhc.encounter uenc
      join mrn_patient_mappings pmap 
      on uenc.patientid=pmap.mrn
      where uenc.race is not null
    )
  )
  select
    source_num, i2b2_num
  from source_query, i2b2_query);


/*******************************************************************************
Admission->ICU Days
*******************************************************************************/
select case when source_num=i2b2_num then 1 else 1/0 end as uhc_icu_days_match_source from(
  with i2b2_query as(
    select count(*) i2b2_num from(
      /* 
      The following is the query generated by i2b2 (blueherondata.qt_query_master)
      */
      SELECT
        patient_num
      FROM BlueHerondata.observation_fact
      WHERE concept_cd IN
        (SELECT concept_cd
        FROM BlueHerondata.concept_dimension
        WHERE concept_path LIKE '\i2b2\UHC\Admission\Length of Stay\ICU Days\%'
        )
      GROUP BY patient_num
      HAVING COUNT(*) >= 1
    )
  ),
  source_query as(
    select count(*) source_num from(
      select
        uenc.patientid
      from
        uhc.encounter uenc
      join mrn_patient_mappings pmap 
      on uenc.patientid=pmap.mrn
      where uenc.icudays is not null
    )
  )
  select
    source_num, i2b2_num
  from source_query, i2b2_query);


/*******************************************************************************
Admission->Readmission
*******************************************************************************/
select case when source_num=i2b2_num then 1 else 1/0 end as uhc_readmit_match_source from(
  with i2b2_query as(
    select count(*) i2b2_num from(
      /* 
      The following is the query generated by i2b2 (blueherondata.qt_query_master)
      */ 
      SELECT
        patient_num
      FROM BlueHerondata.observation_fact
      WHERE concept_cd IN
        (SELECT concept_cd
        FROM BlueHerondata.concept_dimension
        WHERE concept_path LIKE '\i2b2\UHC\Admission\Days to Readmit\%'
        )
      GROUP BY patient_num
      HAVING COUNT(*) >= 1
    )
  ),
  source_query as(
    select count(*) source_num from(
      select
        uenc.patientid
      from
        uhc.encounter uenc
      join mrn_patient_mappings pmap 
      on uenc.patientid=pmap.mrn
      join uhc.readmission uread
      on uenc.recordid=uread.recordid
    )
  )
  select
    source_num, i2b2_num
  from source_query, i2b2_query);

 
/*******************************************************************************
Procedure->CCSICD9Procedure
*******************************************************************************/
select case when source_num=i2b2_num then 1 else 1/0 end as uhc_icd9proc_match_source from(
  with i2b2_query as(
    select count(*) i2b2_num from(
      /* 
      The following is the query generated by i2b2 (blueherondata.qt_query_master)
      */
      SELECT
        patient_num
      FROM BlueHerondata.observation_fact
      WHERE concept_cd IN
        (SELECT concept_cd
        FROM BlueHerondata.concept_dimension
        WHERE concept_path LIKE '\i2b2\UHC\Procedures\CCS ICD9-CM Procedures\%'
        )
      GROUP BY patient_num
      HAVING COUNT(*) >= 1
      )
  ),
  source_query as(
    select count(*) source_num from(
      select 
        distinct(uenc.patientid)
      from 
        uhc.encounter uenc
      join mrn_patient_mappings pmap
      on uenc.patientid=pmap.mrn
      join uhc.procedure proc
      on uenc.recordid=proc.recordid
      where proc.ccsicd9cmproccode is not null
    )
  )
  select
    source_num, i2b2_num
  from source_query, i2b2_query);