sFfQVGb1

1----------------------------
2----------- GRR-------------
3----------------------------
4-- dev_grr schema
5
6-- select qa_tests.purge_cache()
7;
8select qa_tests.get_checks()
9;
10select * from qa_tests.get_summary ('concept')
11;
12select * from qa_tests.get_summary ('concept_relationship')
13;
14select * from qa_tests.get_summary ('concept_relationship') where vocabulary_id_1 = 'GRR' and not ( invalid_reason ='D'
15and concept_delta < 0) ;
16
17select * from qa_tests.get_domain_changes(); -- Domain changes
18
19select * from qa_tests.get_newly_concepts() ; -- Newly added concepts grouped by vocabulary_id and domain
20
21select * from qa_tests.get_standard_concept_changes(); -- Standard concept changes
22
23select * from qa_tests.get_newly_concepts_standard_concept_status();-- Newly added concepts and their standard concept status
24
25select * from qa_tests.get_changes_concept_mapping() ;-- Changes of concept mapping status grouped by target domain
26
27-- new codes
28select concept_code from dev_grr.concept  where vocabulary_id = 'GRR'
29except 
30select concept_code from devv5.concept where vocabulary_id = 'GRR'; -- 1
31
32-- concept_code in concept_name field
33select * from dev_grr.concept where concept_name  ~ '\d+_\d+' and vocabulary_id = 'GRR'; 
34-- old version
35select * from grr_concept_full where concept_name  ~ '\d+_\d+' and vocabulary_id = 'GRR'; 
36
37-- erroneous pairs of concept_class for one concept - should return 0
38with t1 as (
39select distinct c.concept_id as grr_id, c.concept_code as grr_code, c.concept_name as grr_name, 'GRR', r.relationship_id, d.* from dev_grr.concept_relationship r 
40join dev_grr.concept c on concept_id_1 = c.concept_id
41join dev_grr.concept d on d.concept_id = concept_id_2
42where c.vocabulary_id = 'GRR' and c.invalid_reason is null and d.standard_concept = 'S' and r.invalid_reason is null)
43select * from t1 a
44join t1 b on a.grr_id = b.grr_id and a.concept_class_id <> b.concept_class_id; 
45
46-- reverse delta
47select * from devv5.concept c where c.vocabulary_id = 'GRR'
48except 
49select * from concept c where c.vocabulary_id = 'GRR'; -- 1436 (before last check there were 1119)
50
51--missing but alive concept_codes - should return 0
52with t1 as (
53select * from devv5.concept c where c.vocabulary_id = 'GRR'
54except 
55select * from concept c where c.vocabulary_id = 'GRR')
56select * from t1 where  invalid_reason is null and concept_code not in (select concept_code from dev_grr.concept where vocabulary_id = 'GRR')
57; -- 0
58
59-- funny case, names are both in dev_grr and devv5, but not combiations of concept_name||concept_code - ANSWER:source changed this
60with t1 as (
61select * from devv5.concept c where c.vocabulary_id = 'GRR'
62except 
63select * from concept c where c.vocabulary_id = 'GRR')
64select * from t1 where  invalid_reason is null and concept_name !~ '\d\d\d\d\d\d\d'
65and concept_name  in (select concept_name from dev_grr.concept where vocabulary_id = 'GRR' )
66and concept_name||concept_code not in (select concept_code||concept_name from  dev_grr.concept where vocabulary_id = 'GRR');
67
68-- example
69select  * from dev_grr.concept where concept_code = '669594_08012009';
70select  * from devv5.concept where concept_code = '669594_08012009';
71; 
72-- another example - Viola said that this is OK (source changed names)
73select * from devv5.concept c join 
74dev_grr.concept d on c.concept_id = d.concept_id and c.concept_name <> d.concept_name and c.vocabulary_id = 'GRR' 
75and c.concept_name !~ '\d\d\d\d\d\d\d';
76
77/*-- from the first check
78select * from grr_concept_full c where c.vocabulary_id = 'GRR'
79except 
80select * from devv5.concept c where c.vocabulary_id = 'GRR';*/
81
82-- long dosages after fix - however '0's before dots are absent
83select * from dev_grr.concept c where c.vocabulary_id = 'GRR' and concept_name ~ '\d\d\d\d\d\d\d\d\d\d' and invalid_reason is null
84and concept_name in (select concept_name from devv5.concept where vocabulary_id = 'GRR' ); -- 1702
85
86-- possible to fix via update
87select concept_name, regexp_replace (concept_name, '\s+\.', ' 0.', 'g') from dev_grr.concept c where c.vocabulary_id = 'GRR' and concept_name ~ '\d\d\d\d\d\d\d\d\d\d' and invalid_reason is null
88and concept_name in (select concept_name from devv5.concept where vocabulary_id = 'GRR' ); -- 1702
89
90-- name duplicates - Anya confirmed that this is OK (GRR feature)
91select  * from dev_grr.concept where 
92vocabulary_id = 'GRR' and 
93concept_name in (select concept_name from dev_grr.concept where vocabulary_id = 'GRR'  group by concept_name having count (1) >1)
94and invalid_reason is null 
95order by concept_name;
96
97-- delta for mappings
98select distinct c.concept_id, c.concept_name, r.relationship_id, d.concept_id, d.concept_name, d.concept_class_id from dev_grr.concept_relationship r
99join dev_grr.concept c on c.concept_id = r.concept_id_1  and c.vocabulary_id = 'GRR'
100join dev_grr.concept d on d.concept_id = r.concept_id_2 and r.invalid_reason is null and d.standard_concept = 'S'
101except 
102select distinct c.concept_id, c.concept_name, r.relationship_id, d.concept_id, d.concept_name, d.concept_class_id from devv5.concept_relationship r
103join devv5.concept c  on c.concept_id = r.concept_id_1 and c.vocabulary_id = 'GRR'
104join devv5.concept d on d.concept_id = r.concept_id_2 and r.invalid_reason is null and d.standard_concept = 'S'
105;
106
107-- mappings' refresh
108select * from map_diff;
109
110/*drop table if exists map_diff;
111create table map_diff as (
112with new_map as (
113select a.concept_code, a.concept_name, string_agg (r.relationship_id, '-' order by b.concept_code ) as relationship_agg , string_agg (b.concept_code, '-' order by b.concept_code ) as code_agg , string_agg (b.concept_name, '-/-' order by b.concept_code) as name_agg from concept a
114join concept_relationship r on a.concept_id = concept_id_1 and r.relationship_id in ('Maps to', 'Source - RxNorm eq') and r.invalid_reason is null
115join concept b on b.concept_id = concept_id_2 
116where a.vocabulary_id= 'GRR' and a.invalid_reason is null 
117
118group by a.concept_code, a.concept_name 
119)
120, 
121old_map as (
122select a.concept_code, a.concept_name, string_agg (r.relationship_id, '-' order by b.concept_code ) as relationship_agg , string_agg (b.concept_code, '-' order by b.concept_code ) as code_agg , string_agg (b.concept_name, '-/-' order by b.concept_code) as name_agg from devv5. concept a
123join devv5.concept_relationship r on a.concept_id = concept_id_1 and r.relationship_id in ('Maps to') and r.invalid_reason is null
124join devv5.concept b on b.concept_id = concept_id_2 
125where a.vocabulary_id= 'GRR' and a.invalid_reason is null 
126
127group by a.concept_code, a.concept_name 
128)
129select
130a.concept_code as source_code, a.concept_name as source_name,
131a.relationship_agg as old_relat_agg, a.code_agg as old_code_agg, a.name_agg as old_name_agg
132,
133b.relationship_agg as new_relat_agg, b.code_agg as new_code_agg, b.name_agg as new_name_agg from old_map a 
134join new_map b
135on a.concept_code = b.concept_code and (a.code_agg != b.code_agg or a.relationship_agg != b.relationship_agg)
136order by a.concept_code
137); */