P04: Find procedures used for drug administration

This query enables extraction of all procedures that are used for drug administration (such as injectables) and the corresponding drugs being administered. The query does not include any filters for individual drugs or individual procedures and returns a list of all such combinations in the vocabulary.

Input:
 Parameter  Example  Mandatory  Notes
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following is a sample run of the query to extract all procedures used for drug administration.
SELECT SC.source_CODE                                 Procedure_code,
       SC.source_code_description                     Procedure_name, 
       SC.source_vocabulary_id                        Procedure_vocabulary_id,
       VS.vocabulary_name                             Procedure_vocabulary_name,
       NVL(CD2.concept_id, CD.concept_id)             Drug_concept_id,                         
       NVL(CD2.concept_name, CD.concept_name)         Drug_name, 
       NVL(CD2.concept_code, CD.concept_code)         Drug_concept_code,
       NVL(CD2.concept_class, CD.concept_class)       Drug_concept_class, 
       NVL(CD2.vocabulary_id, CD.vocabulary_id)       Drug_vocabulary_id
FROM   vocabulary.source_to_concept_map  SC
  INNER JOIN vocabulary.vocabulary   VS ON SC.source_vocabulary_id = VS.vocabulary_id
  INNER JOIN vocabulary.concept      CD ON SC.target_concept_id = CD.concept_id 
                                      AND sysdate BETWEEN CD.valid_start_date AND CD.valid_end_date
  LEFT OUTER JOIN vocabulary.concept_relationship CR  ON CD.concept_id = CR.concept_id_1 
                                      AND CR.relationship_id = 102
  LEFT OUTER JOIN vocabulary.concept CD2 ON CR.concept_id_2 = CD2.concept_id
WHERE  SC.source_vocabulary_id IN (3,4,5)
AND    lower(SC.mapping_type) = 'procedure drug'
 Output:
Output field list:
 Field  Description
 Procedure_code  Mapped source code of procedure used for drug administration
 Procedure_name  Name of procedure used for drug administration
 Procedure_vocabulary_id  Source vocabulary the procedure code is derived from, expressed as vocabulary ID
 Procedure_vocabulary_name  Name of the vocabulary the procedure code is derived from
 Drug_concept_id  Concept ID of drug being administered by the procedure
 Drug_name  Name of drug being administered by the procedure
 Drug_concept_code  Concept code of drug being administered by the procedure
 Drug_concept_class  Concept class of drug being administered by the procedure
 Drug_vocabulary_id  Vocabulary ID of the vocabulary from which the disease/condition concept is derived from

Sample output record:
 Field  Value
 Procedure_code  A9505
 Procedure_name  Thallous 1 MCI
 Procedure_vocabulary_id  5
 Procedure_vocabulary_name  HCPCS
 Drug_concept_id  19135941
 Drug_name  Thallous chloride Tl201
 Drug_concept_code  91588
 Drug_concept_class  Ingredient
 Drug_vocabulary_id  8
Comments