I am rebuilding our value sets for identifying (in contemporary and historic data) administration of the various pneumococcal vaccines. Vocabularies of primary interest are CVX and RxNorm. If find SNOMED or CPT4 concepts along the way, so much the better.
I started with the CVX code for PCV15 (CID=702674) and pulled its descendants; they all appear to include “diptheria”.
If instead of the concept_ancestor table, I utilize concept_relationhip , Diptheria is again present.
I then tried PCV21.
As, unlike the other CVX codes for pneumococcal vaccines, the CVX code for PCV21 (CID=708297) is not a “S”tandard concept, the concept_ancestor table is not available. However concept relationship is available… and includes diptheria.
I am not a clinician so I am likely confused… but so far as I understand Diptheria is covered solely with DTaP, Tdap, and Td vaccines.
What am I overlooking?
—query using concept_ancestor
select
c.concept_name ,
c.concept_ID ,
c.concept_code ,
c.concept_class_id,
c.vocabulary_id ,
c.domain_id
from
vocabulary.concept_ancestor ca
inner join
vocabulary.concept c
on ca.descendant_concept_id = c.concept_id
where
ca.ancestor_concept_id=702674
;
—query using concept_relationship
select
c.concept_name ,
c.concept_ID ,
c.concept_code ,
c.concept_class_id,
c.vocabulary_id ,
c.domain_id,
cr.relationship_id
from
vocabulary.concept_relationship cr
inner join
vocabulary.concept c
on cr.concept_id_2 = c.concept_id
where
cr.concept_id_1 != cr.concept_id_2 and
cr.concept_id_1=702674
;
Thanks!
G