· 6 years ago · May 22, 2019, 02:22 PM
1--we are only interested to find brand names that have 'stable' ingredient sets: with one possible ingredient combination
2drop table if exists brand_rx
3;
4create table brand_rx as
5with bn_to_i as
6 (
7 select
8 c.concept_id as b_id,
9 r.concept_id_2 as i_id,
10 c.concept_name as concept_name,
11 count (r.concept_id_2) over (partition by c.concept_id) as cnt_direct
12 from concept c
13 join concept_relationship r on
14 r.relationship_id = 'Brand name of' and
15 c.concept_id = r.concept_id_1
16 join concept c2 on
17 c2.concept_class_id = 'Ingredient' and
18 c2.concept_id = r.concept_id_2
19 where
20 c.vocabulary_id in ('RxNorm', 'RxNorm Extension') and
21 c.concept_class_id = 'Brand Name' and
22 c.invalid_reason is null
23 ),
24bn_to_i_dp as --what possible ingredient sets drug products give us
25 (
26 select distinct
27 c.concept_id as b_id,
28 r.concept_id_2 as dp_id,
29 d.ingredient_concept_id as i_id,
30 count (d.ingredient_concept_id) over (partition by r.concept_id_2) as cnt_drug
31 from concept c
32 join concept_relationship r on
33 r.relationship_id = 'Brand name of' and
34 c.concept_id = r.concept_id_1
35 join concept c2 on
36 c2.concept_class_id != 'Ingredient' and --only combinations and ingredient themselves can have brand names;
37 c2.concept_id = r.concept_id_2
38 join drug_strength d on
39 c2.concept_id = d.drug_concept_id
40 where
41 c.vocabulary_id in ('RxNorm', 'RxNorm Extension') and
42 c.concept_class_id = 'Brand Name' and
43 c.invalid_reason is null
44 )
45select distinct
46 b.b_id,
47 b.concept_name,
48 b.i_id
49from bn_to_i b
50left join bn_to_i_dp d on
51 d.b_id = b.b_id and
52 b.cnt_direct > d.cnt_drug
53where d.b_id is null
54;
55insert into brand_rx
56--preserve also bn that are consistent inside RxN
57with bn_to_i as
58 (
59 select
60 c.concept_id as b_id,
61 r.concept_id_2 as i_id,
62 c.concept_name as concept_name,
63 count (r.concept_id_2) over (partition by c.concept_id) as cnt_direct
64 from concept c
65 join concept_relationship r on
66 r.relationship_id = 'Brand name of' and
67 c.concept_id = r.concept_id_1
68 join concept c2 on
69 c2.concept_class_id = 'Ingredient' and
70 c2.concept_id = r.concept_id_2
71 where
72 c.concept_id not in (select b_id from brand_rx) and --avoid duplication
73 c.vocabulary_id = 'RxNorm' and
74 c.concept_class_id = 'Brand Name' and
75 c.invalid_reason is null and
76 exists -- there are RxNorm Drug products with r.concept_id_2 as an ingredient
77 (
78 select
79 from drug_strength d
80 join concept x on
81 d.drug_concept_id = x.concept_id and
82 x.vocabulary_id = 'RxNorm' and
83 x.concept_class_id != 'Ingredient' and
84 d.ingredient_concept_id = r.concept_id_2
85 )
86 ),
87bn_to_i_dp as --what possible ingredient sets drug RxN products give us
88 (
89 select distinct
90 c.concept_id as b_id,
91 r.concept_id_2 as dp_id,
92 d.ingredient_concept_id as i_id,
93 count (d.ingredient_concept_id) over (partition by r.concept_id_2) as cnt_drug
94 from concept c
95 join concept_relationship r on
96 r.relationship_id = 'Brand name of' and
97 c.concept_id = r.concept_id_1
98 join concept c2 on
99 c2.concept_class_id != 'Ingredient' and --only combinations and ingredient themselves can have brand names;
100 c2.concept_id = r.concept_id_2
101 join drug_strength d on
102 c2.concept_id = d.drug_concept_id
103 where
104 c.concept_id not in (select b_id from brand_rx) and --avoid duplication
105 c.vocabulary_id = 'RxNorm' and
106 c.concept_class_id = 'Brand Name' and
107 c.invalid_reason is null
108 )
109select distinct
110 b.b_id,
111 b.concept_name,
112 b.i_id
113from bn_to_i b
114left join bn_to_i_dp d on
115 d.b_id = b.b_id and
116 b.cnt_direct > d.cnt_drug
117where d.b_id is null