This query provides the brands that are used for a generic drug. The input to the query is
a value of a drug_concept_id. See
vocabulary queries for obtaining valid drug_concept_id values.
Note that depending on the mapping available for the source_values in the drug_exposure table, branded drug information might only partially or not be provided. See the Standard Vocabulary Specifications at
Input:
| Parameter |
Example |
Mandatory |
Notes |
| drug_concept_id |
19019306 |
Yes |
Nicotine Patch
|
Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue.
SELECT tt.drug_name, tt.brand_name,
100.00*tt.part_brand/tt.total_brand as perc_brand_count
FROM (
SELECT
t.drug_name, t.brand_name,
t.cn_3_02 part_brand,
SUM(t.cn_3_02) OVER() total_brand
FROM (
SELECT sum((select count(1) from drug_exposure d where d.drug_concept_id = cr003.concept_id_2)) cn_3_02,
A.Concept_Name drug_name,
D.Concept_Name brand_name
FROM vocabulary.concept_relationship CR003,
vocabulary.concept A,
vocabulary.concept_relationship CR007,
vocabulary.concept_relationship CR006,
vocabulary.concept D
WHERE CR003.relationship_ID = 3
AND CR003.concept_id_1 = A.concept_id
AND lower(A.concept_class) = 'clinical drug'
AND CR007.concept_id_2 = CR003.concept_id_2
AND CR007.relationship_ID = 7
AND CR007.concept_id_1 = CR006.concept_id_1
AND CR006.relationship_ID = 6
AND CR006.concept_id_2 = D.concept_id
AND lower(D.concept_class) = 'brand name'
AND A.concept_Id = 19019306
GROUP BY
A.Concept_Name ,
D.Concept_Name
) t
) tt
WHERE tt.total_brand > 0
;
Output:
Output field list:
| Field |
Description |
| drug_name |
The name of the query drug
|
| brand_name |
The name of the brand
|
perc_brand_count
|
The market share for each brand
| Sample output record: | Field | Content | | drug_name |
| | brand_name |
| perc_brand_count
|
|
|
|
