InformaticsMetricACTOracle.sql

-- CTSA CLIC Metric 
-- CDM: i2b2/ACT 
-- RDB Dialect: Oracle
-- Creation Date: 4/1/2021
-- Update Date: 03/06/22
-- Ontology Version: 4.0

-- Caveats:  ACT does not support the following by default, placeholders have been provided if you support the domain locally
--           NLP, Notes, SNOMED Procedures or Diagnosis
-- Multifact table
-- Indexes
DEFINE ONE_YEAR_MIN_DATE = '01-JAN-2021';
DEFINE FIVE_YEAR_MIN_DATE = '01-JAN-2017';
DEFINE MAX_DATE = '31-DEC-2021';

SELECT TO_CHAR (SYSDATE, 'MON DD HH24:MI:SS') FROM DUAL;

--select * from CTSA_CLIC_METRIC;
-- Create dest table
RENAME CTSA_CLIC_METRIC TO CTSA_CLIC_METRIC_2020;
CREATE TABLE CTSA_CLIC_METRIC (
	variable_name			VARCHAR(100)  NOT NULL,
    one_year            INT   NULL,
	five_year		    INT   NULL
);
COMMIT;

--  Variable: data_model
--  Acceptable values:  1=PCORNet, 2=OMOP, 3=TriNetX, 4=i2b2/ACT

INSERT INTO ctsa_clic_metric
SELECT 
	'data_model' as variable_name
	,'4' as one_year -- 4 = i2b2/ACT
	,'4' as five_year -- 4 = i2b2/ACT
FROM dual;

--Edit this for your site 
--change the edit_this_for_your_site to '0', '1' or null
-- this will error out if not modified
--Answers '0' your site does not have NLP /Notes capability, '1' your site does have NLP / Notes does  have NLP /Notes capability NULL if the model does not support  NLP
INSERT INTO ctsa_clic_metric
SELECT
	'nlp_any' as variable_name
	, edit_this_for_your_site as one_year
	, edit_this_for_your_site as five_year
FROM dual;

-- Unique ENCOUNTERS 
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'total_encounters' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.ENCOUNTER_NUM) CNT
        FROM OBSERVATION_FACT OBS WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.ENCOUNTER_NUM) CNT
        FROM OBSERVATION_FACT OBS WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;


-- Unique PATIENTS 
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'total_patients' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- Unique PATIENTS with gender
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'uniq_pt_with_gender' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND (PAT.SEX_CD IS NULL OR PAT.SEX_CD <> 'DEM|SEX:NI')
        WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND (PAT.SEX_CD IS NULL OR PAT.SEX_CD <> 'DEM|SEX:NI')
        WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- Unique PATIENTS WITH BIRTH DATES
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'uniq_pt_with_age' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND PAT.BIRTH_DATE IS NOT NULL
        WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND PAT.BIRTH_DATE IS NOT NULL
        WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- UNIQUE PATIENTS OVER 12
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'total_pt_gt_12' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
        WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
        WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- UNIQUE PATIENTS WITH IMPLAUSIBLE AGES (TOO YOUNG)
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'uniq_pt_birthdate_in_future' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND PAT.BIRTH_DATE > TO_DATE('&MAX_DATE')
        WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM AND PAT.BIRTH_DATE > TO_DATE('&MAX_DATE')
        WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- UNIQUE PATIENTS WITH IMPLAUSIBLE AGES (TOO OLD)
INSERT INTO CTSA_CLIC_METRIC
SELECT
  'uniq_pt_age_over_120' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
        AND ((PAT.DEATH_DATE IS NULL AND TO_DATE(TO_DATE('&MAX_DATE'), 'DD-MON-YYYY') - PAT.BIRTH_DATE > 120*365)
            OR (PAT.DEATH_DATE - PAT.BIRTH_DATE > 120*365))
        WHERE OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
       FROM OBSERVATION_FACT OBS
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
        AND ((PAT.DEATH_DATE IS NULL AND TO_DATE(TO_DATE('&MAX_DATE'), 'DD-MON-YYYY') - PAT.BIRTH_DATE > 120*365)
            OR (PAT.DEATH_DATE - PAT.BIRTH_DATE > 120*365))
        WHERE OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;



-- Unique patients with procedures mapped to SNOMED -- THIS IS NOT SUPPORTED BY ACT - IF YOU HAVE THEM MODIFY PATH ACCORDINGLY 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\SNOMED\%'
)
SELECT
  'uniq_pt_snomed_proc' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;


-- Unique patients with DIAGNOSIS mapped to SNOMED -- THIS IS NOT SUPPORTED BY ACT - IF YOU HAVE THEM MODIFY PATH ACCORDINGLY 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\SNOMED\%'
)
SELECT
  'uniq_pt_snomed_dx' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;



-- Unique patients with a Note -- Not supported by ACT - Change path if your site supports it 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Notes\%'
)
SELECT
  'uniq_pt_note' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;




-- Unique patients with INSURANCE mapped to STANDARDIZED VALUES
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\SDOH\76437-3\%'
)
SELECT
  'uniq_pt_insurance_value_set' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- Unique patients with INSURANCE - UNMAPPED INSURANCE IS NOT COUNTABLE AT THIS TIME
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\SDOH\76437-3\%'
)
SELECT
  'uniq_pt_any_insurance_value' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- Unique patients with procedures mapped to ICD 10/9 -- 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Procedures\ICD9\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Procedures\ICD10\%'
)
SELECT
  'uniq_pt_icd_proc' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;


-- Unique patients with labs mapped to LOINC 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_LABS AS (
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Labs\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Lab\%'
)
SELECT
  'uniq_pt_loinc' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_LABS MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_LABS MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;




-- Unique patients with medications mapped to RXNORM or NDC 
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_LABS AS (
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\%'
)
SELECT
  'uniq_pt_med_rxnorm' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_LABS MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_LABS MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;



-- Unique patients with diagnosis and conditions mapped to ICD9/10
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Diagnosis\%'
)
SELECT
  'uniq_pt_icd_dx' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;



-- Unique patients with procedures mapped to HCPCS and CPT4
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Procedures\HCPCS\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Procedures\CPT4\%'
)
SELECT
  'uniq_pt_cpt' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;


-- Unique patients with smoking data mapped
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\SDOH\LP156992-2\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Diagnosis\ICD10\V2_2018AA\A20098492\A20170670\A18921523\A17812661\%'
)
SELECT
  'uniq_pt_smoking' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
        JOIN PATIENT_DIMENSION PAT ON PAT.PATIENT_NUM = OBS.PATIENT_NUM 
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;

-- Unique patients with opioid abuse disorder
INSERT INTO CTSA_CLIC_METRIC
WITH MAPPED_DOMAIN AS (      
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\BPreparations\1819\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\BPreparations\352364\%' 
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\MPreparations\6813\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\NPreparations\1007909\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\NPreparations\7242\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\BPreparations\352364\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\NPreparations\214721\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Medications\MedicationsByAlpha\V2_12112018\RxNormUMLSRxNav\NPreparations\1545902\%'
        UNION
    SELECT CONCEPT_CD FROM  CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Diagnosis\ICD10\V2_2018AA\A20098492\A20170670\A18921523\A17774215\%' -- OPIOID DISORDER
)
SELECT
  'uniq_pt_opioid' AS VARIABLE_NAME,
  
   ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&ONE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS ONE_YEAR,
    
    ( SELECT COUNT(DISTINCT OBS.PATIENT_NUM) CNT
        FROM MAPPED_DOMAIN MAP
        JOIN OBSERVATION_FACT OBS ON OBS.CONCEPT_CD = MAP.CONCEPT_CD AND OBS.START_DATE BETWEEN TO_DATE('&FIVE_YEAR_MIN_DATE') AND TO_DATE('&MAX_DATE')
    ) AS FIVE_YEAR
FROM DUAL;
COMMIT;


--SELECT * FROM CTSA_CLIC_METRIC;
--RENAME CTSA_CLIC_METRIC TO CTSA_CLIC_METRIC_2021;


INSERT INTO CTSA_CLIC_METRIC 
SELECT VARIABLE_NAME, ONE_YEAR, FIVE_YEAR FROM (
WITH TOP_CODES AS 
(SELECT 'ICD10CM:D64.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E03.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E11.65' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E11.65' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E11.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E55.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E66.01' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E66.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E78.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E78.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:E78.5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F17.200' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F17.210' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F17.210' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F32.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F41.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:F41.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:G47.33' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:G89.29' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:I10' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:I25.10' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:I48.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:I48.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:I48.91' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:J02.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:J06.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:J44.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:J45.909' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:K21.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:M19.90' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:M25.561' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:M25.562' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:M54.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:M54.5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:N18.3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R05' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R06.02' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R07.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R10.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R53.83' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:R94.31' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Y92.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Y93.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z00.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z00.129' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z01.419' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z01.818' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z12.11' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z12.31' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z23' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z51.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z51.11' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z71.3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z79.01' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z79.4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z79.82' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z79.899' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z87.891' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10CM:Z98.890' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:174.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:244.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:250.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:268.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:272.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:272.4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:278.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:285.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:300.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:305.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:311' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:327.23' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:401.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:401.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:414.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:414.01' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:427.31' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:428.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:462' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:465.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:477.9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:493.90' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:496' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:530.81' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:599.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:715.90' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:719.46' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:724.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:724.5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:729.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:729.5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:733.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:780.79' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:784.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:786.05' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:786.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:786.50' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:789.00' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V03.82' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V04.81' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V06.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V10.3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V20.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V22.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V22.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V22.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V22.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V43.3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V45.89' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V54.89' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V58.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V58.11' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V58.61' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V58.69' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V67.09' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V70.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V72.31' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V76.12' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9CM:V76.2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:10466-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1742-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1744-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1751-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:17861-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:19123-9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1920-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1960-4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1962-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1963-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:1975-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2019-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2028-9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:20564-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:20570-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2075-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2160-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2339-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2345-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:26450-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:26485-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2703-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2708-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2711-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2713-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2744-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2777-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2823-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2885-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:2951-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:30180-4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:3094-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:3097-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:32623-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:33037-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:33914-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:4544-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:48642-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:48643-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:51731-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:58413-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:5902-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:5905-5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:61152-5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:62238-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:6301-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:6463-4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:66746-9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:6690-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:6768-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:704-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:706-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:711-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:713-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:71695-1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:718-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:731-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:736-9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:742-7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:751-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:770-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:777-3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:785-6' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:786-4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:787-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:788-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:789-8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:8310-5' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:933-2' AS CONCEPT_CD FROM DUAL UNION
SELECT 'LOINC:934-0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:834023' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:2418' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:6918' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:104491' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:105029' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1050490' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1189779' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1246235' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1360292' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:141928' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1495474' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1495476' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:152698' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:152923' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:153892' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1664448' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:205770' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:206765' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:206766' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:206821' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:208161' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:209387' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:209459' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:212340' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:212549' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:212575' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:213169' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:261224' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:284400' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:311036' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:540472' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:540848' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:541713' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:541815' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:541872' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:542673' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:542678' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:617314' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:617318' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:617320' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:628530' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:824194' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:828350' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:835605' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:859096' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:861006' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:861008' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:863671' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:865098' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:866516' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:967525' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1049221' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:998740' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1115005' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1361615' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1437702' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1049621' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:876193' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:866924' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:857002' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1658634' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1659137' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1665088' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1732006' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1735003' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1735004' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1740467' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1807628' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1807632' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1807633' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:198440' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:310273' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:310431' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:313782' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:314200' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:318272' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:617311' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:834127' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:854235' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1797907' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1795964' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:901044' AS CONCEPT_CD FROM DUAL UNION
SELECT 'RXNORM:1490493' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:00790' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:01967' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:36415' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:70450' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:71045' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80048' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80051' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80053' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80061' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80076' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81000' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81002' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81003' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82043' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82306' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82310' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82330' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82565' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82607' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82805' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82947' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82962' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83036' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83605' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83615' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83690' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83735' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84100' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84132' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84153' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84295' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84439' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84443' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84450' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84460' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84484' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84520' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84550' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84999' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85004' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85014' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85025' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85027' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85610' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85652' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85730' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86140' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86850' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86900' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86901' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87040' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87070' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87086' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88185' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88305' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88399' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:90460' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:90471' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93000' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93005' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93010' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:94640' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:95004' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:97530' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99024' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99203' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99212' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99213' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99214' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99215' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99232' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99233' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99244' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99283' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99284' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99285' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:00790' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:01967' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:36415' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:70450' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:71045' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80048' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80051' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80053' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80061' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:80076' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81000' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81002' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:81003' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82043' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82306' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82310' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82330' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82565' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82607' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82805' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82947' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:82962' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83036' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83605' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83615' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83690' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:83735' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84100' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84132' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84153' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84295' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84439' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84443' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84450' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84460' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84484' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84520' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84550' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:84999' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85004' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85014' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85025' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85027' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85610' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85652' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:85730' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86140' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86850' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86900' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:86901' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87040' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87070' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:87086' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88185' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88305' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:88399' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:90460' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:90471' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93000' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93005' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:93010' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:94640' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:95004' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:97530' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99024' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99201' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99202' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99203' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99204' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99205' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99211' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99212' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99213' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99214' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99215' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99221' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99222' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99223' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99224' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99225' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99226' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99231' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99232' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99233' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99234' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99235' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99236' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99238' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99239' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99241' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99242' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99243' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99244' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99245' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99251' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99252' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99253' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99254' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99255' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99281' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99282' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99283' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99284' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99285' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99288' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99291' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99292' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99304' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99305' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99306' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99307' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99308' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99309' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99310' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99315' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99316' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99318' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99324' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99325' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99326' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99327' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99328' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99334' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99335' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99336' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99337' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99339' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99340' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99341' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99342' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99343' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99344' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99345' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99347' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99348' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99349' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99350' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99354' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99355' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99356' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99357' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99358' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99359' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99360' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99366' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99367' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99368' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99374' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99375' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99377' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99378' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99379' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99380' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99381' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99382' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99383' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99384' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99385' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99386' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99387' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99391' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99392' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99393' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99394' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99395' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99396' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99397' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99401' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99402' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99403' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99404' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99406' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99407' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99408' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99409' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99411' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99412' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99415' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99416' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99421' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99422' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99423' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99429' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99441' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99442' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99443' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99446' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99447' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99448' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99449' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99451' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99452' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99453' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99454' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99455' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99456' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99457' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99458' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99460' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99461' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99462' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99463' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99464' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99465' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99466' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99467' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99468' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99469' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99471' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99472' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99473' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99474' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99475' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99476' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99477' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99478' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99479' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99480' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99483' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99484' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99485' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99486' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99487' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99489' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99490' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99491' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99492' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99493' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99494' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99495' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99496' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99497' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99498' AS CONCEPT_CD FROM DUAL UNION
SELECT 'CPT4:99499' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:A7003' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:A9585' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:A9999' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:E1399' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0008' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0009' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0105' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0121' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0378' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:G0444' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0131' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0133' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0171' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0461' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0583' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0585' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0690' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0692' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0712' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J0878' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1030' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1040' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1050' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1100' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1442' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1642' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1644' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1650' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1756' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1815' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J1953' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2001' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2185' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2248' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2250' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2370' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2405' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2543' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2704' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2710' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J2795' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3010' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3301' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3420' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3475' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3480' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J3490' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7030' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7185' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7187' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7189' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7192' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7195' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7323' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7512' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J7613' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J8540' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J9250' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J9263' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:J9267' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:Q0091' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:Q0162' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:Q5101' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:Q9959' AS CONCEPT_CD FROM DUAL UNION
SELECT 'HCPCS:Q9967' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:009U3ZX' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:01NB0ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:027034Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:02HV33Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:03HY32Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0BH17EZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0BJ08ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0CJS8ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0DB68ZX' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0DB98ZX' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0DH67UZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0DJ08ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0DJD8ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0FT44ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0HQ9XZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0KQM0ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0SRC0J9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0SRD0J9' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0T9B70Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0U7C7ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0VTTXZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0W9930Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:0W9B30Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:10907ZC' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:10D00Z1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:10E0XZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:30233K1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:30233N1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:30233R1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:3E033VJ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:3E0436Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:3E0G76Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:4A023N7' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:4A10X4G' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A1221Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A1935Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A1945Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A1955Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A1D70Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:5A2204Z' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:8E0W4CZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B020ZZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B030ZZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B2111ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B2151ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B24BZZ4' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:B32R1ZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:BW28ZZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD10PCS:HZ2ZZZZ' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:00.40' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:00.66' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:03.31' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:03.91' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:03.92' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:04.81' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:13.41' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:13.71' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:33.24' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:37.22' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:38.91' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:38.93' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:38.97' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:39.95' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:42.92' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:45.13' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:45.16' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:45.23' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:45.25' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:45.42' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:51.23' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:54.91' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:57.32' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:57.94' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:59.8' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:64.0' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:73.59' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:74.1' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:75.69' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:80.51' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:81.54' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:81.62' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:81.92' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:86.04' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:86.3' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:86.59' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:87.03' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:88.41' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:88.53' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:88.56' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:88.72' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:93.39' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:93.54' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:93.83' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:96.04' AS CONCEPT_CD FROM DUAL UNION
SELECT 'ICD9PROC:96.71' AS CONCEPT_CD FROM DUAL
),
VOCAB_CODES AS (
     SELECT SUBSTR(CONCEPT_CD, 1,INSTR(CONCEPT_CD,':')-1) SRC_VOCABULARY_ID,
            CONCEPT_CD 
     FROM TOP_CODES
     ORDER BY CONCEPT_CD),
DENOMINATOR AS (
     SELECT SRC_VOCABULARY_ID,
            COUNT(DISTINCT CC.CONCEPT_CD) AS DENOMINATOR
     FROM VOCAB_CODES CC
     GROUP BY SRC_VOCABULARY_ID),
ONE_YEAR_NUMERATOR AS (
     SELECT C.SRC_VOCABULARY_ID,
            C.CONCEPT_CD,
            COUNT(DISTINCT PATIENT_NUM) AS DISTINCT_PATIENTS
     FROM VOCAB_CODES C
     LEFT OUTER JOIN OBSERVATION_FACT O ON C.CONCEPT_CD = O.CONCEPT_CD
     WHERE O.START_DATE BETWEEN '01-JAN-21' AND '31-DEC-21'
     GROUP BY C.SRC_VOCABULARY_ID, C.CONCEPT_CD),
FIVE_YEAR_NUMERATOR AS (
     SELECT C.SRC_VOCABULARY_ID,
            C.CONCEPT_CD,
            COUNT(DISTINCT PATIENT_NUM)  AS DISTINCT_PATIENTS
     FROM VOCAB_CODES C
     LEFT OUTER JOIN OBSERVATION_FACT O ON C.CONCEPT_CD = O.CONCEPT_CD
     WHERE O.START_DATE BETWEEN '01-JAN-17' AND '31-DEC-21'
     GROUP BY C.SRC_VOCABULARY_ID, C.CONCEPT_CD),
ONE_YEAR_NUM_BY_VOCAB AS (
     SELECT SRC_VOCABULARY_ID,
            COUNT(*) NUMERATOR 
     FROM ONE_YEAR_NUMERATOR 
     GROUP BY SRC_VOCABULARY_ID
),
FIVE_YEAR_NUM_BY_VOCAB AS (
     SELECT SRC_VOCABULARY_ID,
            COUNT(*) NUMERATOR 
     FROM FIVE_YEAR_NUMERATOR 
     GROUP BY SRC_VOCABULARY_ID
)
SELECT lower(D.SRC_VOCABULARY_ID || '_CHECK') VARIABLE_NAME,
       TRUNC(100*(O.NUMERATOR/D.DENOMINATOR)) ONE_YEAR,
       TRUNC(100*(F.NUMERATOR/D.DENOMINATOR)) FIVE_YEAR
FROM DENOMINATOR D
LEFT OUTER JOIN ONE_YEAR_NUM_BY_VOCAB O ON O.SRC_VOCABULARY_ID = D.SRC_VOCABULARY_ID
LEFT OUTER JOIN FIVE_YEAR_NUM_BY_VOCAB F ON F.SRC_VOCABULARY_ID = D.SRC_VOCABULARY_ID
);

select TO_CHAR (sysdate, 'MON DD HH24:MI:SS') from dual;
--select * from ctsa_clic_metric;