The following query contains the coverage for mapped source vocabularies in the Condition domains to SNOMED-CT.
Input:
<None>
Sample query run:
The following query returns list of statistics.
SELECT
mapped.vocabulary_id,
mapped.vocabulary_name,
(CASE mapped.concept_level WHEN 0 THEN 'Not mapped' ELSE to_char(mapped.concept_level, '9999') END) AS concept_level,
mapped.mapped_codes,
sum(mapped.mapped_codes) over (partition by vocabulary_id) as total_mapped_codes,
to_char(mapped.mapped_codes*100/sum(mapped.mapped_codes) over (partition by vocabulary_id), '990.9') AS pct_mapped_codes,
mapped.mapped_concepts,
(SELECT count(8)
FROM vocabulary.concept
WHERE vocabulary_id=1
AND concept_level=mapped.concept_level
AND concept_class='Clinical finding'
AND invalid_reason is null)
AS concepts_in_level,
to_char(mapped.mapped_concepts*100/(
SELECT CASE count(8) WHEN 0 THEN 1e16 ELSE count(8) END
FROM vocabulary.concept
WHERE vocabulary_id=1
AND concept_level=mapped.concept_level
AND concept_class='Clinical finding'
AND invalid_reason is null
), '990.9') AS pct_mapped_concepts
FROM (
SELECT
m.source_vocabulary_id AS vocabulary_id,
v.vocabulary_name,
c.concept_level,
COUNT(8) AS mapped_codes,
COUNT(DISTINCT target_concept_id) AS mapped_concepts
FROM vocabulary.source_to_concept_map m
JOIN vocabulary.concept c on c.concept_id=m.target_concept_id
JOIN vocabulary.vocabulary v on v.vocabulary_id=m.source_vocabulary_id
WHERE m.target_vocabulary_id=1
AND lower(m.mapping_type)='condition'
GROUP BY m.source_vocabulary_id, v.vocabulary_name, c.concept_level
) mapped
Output:
Output field list:
Field |
Description |
vocabulary_id |
Source Vocabulary ID |
vocabulary_name |
Source Vocabulary name |
concept_level |
Concept Level Number |
mapped_codes |
Number of mapped codes |
total_mapped_codes |
Total number of mapped codes for source vocabulary |
pct_mapped_codes | Percentile of mapped code | mapped_concepts | Number of mapped concepts | concepts_in_level | Number of mapped concepts | pct_mapped_concepts | Percentile of of mapped concepts |
Sample output record:
Field |
Value |
vocabulary_id |
2 |
vocabulary_name |
ICD9-CT |
concept_level |
1 |
mapped_codes |
4079 |
total_mapped_codes |
10770 |
pct_mapped_codes | 37.0 | mapped_concepts | 3733 | concepts_in_level | 69280 | pct_mapped_concepts | 5.0 |
|
|