Drug Exposure‎ > ‎

DEX20: How many people taking a drug for a given indicaton actually have that disease in their record prior to exposure?

 Parameter  Example  Mandatory  Notes
concept_name Acute Tuberculosis Yes

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue

SELECT count(*) AS treated, sum( NVL( diagnosed, 0 ) ) AS diagnosed
  FROM /* person and tuberculosis treatment start date */
     ( SELECT person_id, min( drug_exposure_start_date ) AS treatment_start
         FROM drug_exposure
         JOIN /* indication and associated drug ids */
            ( SELECT indication.concept_name, drug.concept_id
                FROM vocabulary.concept indication
                JOIN vocabulary.concept_ancestor ON ancestor_concept_id = indication.concept_id
                JOIN vocabulary.vocabulary indication_vocab 
                  ON indication_vocab.vocabulary_id = indication.vocabulary_id
                JOIN vocabulary.concept drug ON drug.concept_id = descendant_concept_id
                JOIN vocabulary.vocabulary drug_vocab ON drug_vocab.vocabulary_id = drug.vocabulary_id
               WHERE indication_vocab.vocabulary_name = 'FDB Indication/Contraindication'
                 AND indication.concept_class         = 'Indication or Contra-indication'
                 AND indication.concept_name          = 'Acute Tuberculosis'
          AND drug_vocab.vocabulary_name       = 'RxNorm'
                 AND drug.concept_level = 1
                 AND sysdate BETWEEN drug.valid_start_date AND drug.valid_end_date
            ) ON concept_id = drug_concept_id
        GROUP BY person_id
     ) treated
  LEFT OUTER JOIN /* patient with Acute Tuberculosis diagnosis */
     ( SELECT person_id, min( condition_start_date ) first_diagnosis, 1 AS diagnosed
         FROM condition_occurrence
         JOIN vocabulary.source_to_concept_map ON target_concept_id = condition_concept_id
         JOIN vocabulary.vocabulary ON vocabulary_id = source_vocabulary_id
        WHERE source_code like '011.%'
          AND vocabulary_name = 'ICD-9-CM'
        GROUP BY person_id
     ) diagnosed
    ON treated.person_id = diagnosed.person_id AND first_diagnosis <= treatment_start;

Output field list:
 Field  Description
concept_name The reason the medication was stopped, where available. Reasons include regimen completed, changed, removed, etc.
drug_concept_id A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.
descendant_concept_id A foreign key to the predefined concept identifier in the vocabulary reflecting the type of drug exposure recorded. It indicates how the drug exposure was represented in the source data: as medication history, filled prescriptions, etc.
valid_start_date The date when the concept was first recorded.
valid_end_date The date when the concept became invalid because it was deleted or superseded (updated) by a new concept. The default value is 31-Dec-2099.

Sample output record:
 Field  Description