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’
Input:
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:
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 |
|