Drug Queries‎ > ‎

D25: Find the generic drugs in a list of drugs

This query is designed to identify generic drug concepts among from a list of standard drug concept IDs. The query identifies branded drugs from the CONCEPT table based on a concept class setting of ‘Clinical Drug’

Input:
 Parameter  Example  Mandatory  Notes
 Drug Concept ID list  1396833, 19060643  Yes List of drug concept id’s
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following are sample runs of the query to extract generic drug concepts from a list of drug concepts entered as input. The input parameters are highlighted in blue.
SELECT C.concept_id        drug_concept_id,
       C.concept_name      drug_name,
       C.concept_code      drug_concept_code,
       C.concept_class     drug_concept_class,
       C.vocabulary_id     drug_vocabulary_id,
       V.vocabulary_name   drug_vocabulary_name
FROM   vocabulary.concept         C,
       vocabulary.vocabulary      V
WHERE  C.vocabulary_id        = 8
AND    C.concept_id IN (1396833, 19060643)
AND    lower(C.concept_class) = 'clinical drug'
AND    C.vocabulary_id        = V.vocabulary_id
AND    sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output:
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of generic drug or pack
 Drug_Name  Name of generic drug or pack
 Drug_Concept_Code  Concept code of generic drug or pack
 Drug_Concept_Class  Concept class of generic drug or pack
 Drug_Vocabulary_ID  Vocabulary the generic drug concept has been derived from, expressed as vocabulary ID
 Drug_Vocabulary_Name  Name of the Vocabulary the generic drug concept has been derived from

Sample output record:
 Field  Value
 Drug_Concept_ID  19060643
 Drug_Name  Budesonide 0.05 MG/ACTUAT Nasal Spray
 Drug_Concept_Code  247042
 Drug_Concept_Class  Clinical Drug
 Drug_Vocabulary_ID  8
 Drug_Vocabulary_Name  RxNorm
Comments