Drug Queries‎ > ‎

D24: Find the branded drugs in a list of drugs

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

 Parameter  Example  Mandatory  Notes
 Drug Concept ID list  1516830, 19046168  Yes List of drug concept id’s
 As of date  '01-Jan-2010'  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 branded drugs 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 (1516830, 19046168)
AND    lower(C.concept_class) = 'branded drug'
AND    C.vocabulary_id        = V.vocabulary_id
AND    '01-jan-2010' BETWEEN C.valid_start_date AND C.valid_end_date
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of branded drug or pack
 Drug_Name  Name of branded drug or pack
 Drug_Concept_Code  Concept code of branded drug or pack
 Drug_Concept_Class  Concept class of branded drug or pack
 Drug_Vocabulary_ID  Vocabulary the branded drug concept has been derived from, expressed as vocabulary ID
 Drug_Vocabulary_Name  Name of the Vocabulary the branded drug concept has been derived from

Sample output record:
 Field  Value
 Drug_Concept_ID  19046168
 Drug_Name  Triamcinolone 0.055 MG/ACTUAT Nasal Spray [Nasacort AQ]
 Drug_Concept_Code  211501
 Drug_Concept_Class  Branded Drug
 Drug_Vocabulary_ID  8
 Drug_Vocabulary_Name  RxNorm