QA Bin Definitions

Overview

QA Bin Definitions

Parameters :terminology (e.g. NCIMTH) and :version (e.g. latest) are allowed in the queries.\

TODO:

Look up definitions of bins (e.g. logically what do they do, we'll need descriptions too
Write up description
Write and test query (with :terminology/:version interpolations)

REQUIRED bins

nci_merge: Merged SCUI current version NCI atoms

select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c
where a.terminology = :terminology
  and a.id = b.concepts_id and b.atoms_id = c.id
  and c.terminology='NCI'
group by a.id having count(distinct c.conceptId)>1

nci_sub_split: Split SCUI current version NCI (or sub-source) atoms

TBD: 
select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c
where a.terminology = :terminology
  and a.id = b.concepts_id and b.atoms_id = c.id 
  and c.terminology in (select terminology from root_terminologies where family = 'NCI' and terminology != 'NCI')
group by a.id having count(distinct c.conceptId)>1

sct_sepfnpt: SNOMED concept clusters where the FN and PT terms are separated

-- What put as clusterId/conceptId?
 
SELECT DISTINCT
    c.id conceptId,
    a.id,
    a.termType,
    a.conceptId,
    c1.id conceptId1,
    a1.id,
    a1.termType,
    a1.conceptId
FROM
    concepts c,
    concepts_atoms ca,
    atoms a,
    concepts c1,
    concepts_atoms ca1,
    atoms a1
WHERE
    c.terminology = 'SNOMEDCT_US'
        AND c1.terminology = 'SNOMEDCT_US'
        AND c.id = ca.concepts_id
        AND ca.atoms_id = a.id
        AND c1.id = ca1.concepts_id
        AND ca1.atoms_id = a1.id
        AND a.termType = 'FN'
        AND a1.termType = 'PT'
        AND a.conceptId = a1.conceptId
        AND c.id != c1.id

cdsty_coc: Find concepts with Clinical Drug STY and any other STY

SELECT DISTINCT
    cs.concepts_id conceptId
FROM
    concepts_semantic_type_components cs
WHERE
    cs.concepts_id IN (SELECT 
            cs.concepts_id
        FROM
            concepts_semantic_type_components cs,
            semantic_type_components s
        WHERE
            cs.semanticTypes_id = s.id
                AND s.semanticType = 'Clinical Drug')
GROUP BY cs.concepts_id
HAVING COUNT(cs.concepts_id) > 1;

multsty: concepts with more than 3 STYs|

SELECT DISTINCT
    cs.concepts_id conceptId
FROM
    concepts_semantic_type_components cs,
    semantic_type_components s
WHERE
    cs.semanticTypes_id = s.id
GROUP BY cs.concepts_id
HAVING COUNT(cs.concepts_id) > 3

styisa: One STY is an ancestor of another in the STY isa hierarchy
```
SemanticType relationships?
```
sfo_lfo: Short form in one concept, long form in another
```
ShortForm/LongForm of what object?
```
deleted_cui: CUIs that are going away - will need bequeathal rel
```
 
unpublishable NCIMTH concepts?
```

NON-required bins

rxnorm_merge: RXCUI Merges

Same as nci_merge but with RXNORM

select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c 
where a.terminology = :terminology   
and a.id = b.concepts_id   and b.atoms_id = c.id    
and c.terminology='RXNORM'  
group by a.id 
having count(distinct c.conceptId)>1

cbo_merge: Merged SCUI current version CBO atoms

Same as nci_merge but with CBO

select a.id clusterId, a.id conceptId 
from concepts a, concepts_atoms b, atoms c 
where a.terminology = :terminology   
and a.id = b.concepts_id   and b.atoms_id = c.id    
and c.terminology='CBO'  
group by a.id 
having count(distinct c.conceptId)>1

mdr_merge: Merged SDUI current version MDR atoms

If SDUI, return descriptor ID instead of conceptId?

pdq_merge: Merged SDUI current version PDQ atoms
sct_sepfnpt: SNOMED concept clusters where the FN and PT terms are separated
- DUPLICATE of above
rxnorm_split: RXCUI splits
```
see split query above
```

nci_pdq_merge: Concepts containing current version NCI and current version PDQ atoms

SELECT DISTINCT 
    ca.concepts_id conceptId
FROM
    atoms a,
    atoms a1,
    concepts_atoms ca,
    concepts_atoms ca1,
    terminologies t,
    terminologies t1
WHERE
    ca.atoms_id = a.id
        AND ca1.atoms_id = a1.id
        AND a.terminology = 'NCI'
        AND a.terminology = t.terminology
        AND a.version = t.version
        AND t.current = true
        AND a1.terminology = 'PDQ'
        AND a1.terminology = t1.terminology
        AND a1.version = t1.version
        AND t1.current = true        
        AND ca.concepts_id = ca1.concepts_id

nci_sct_merge: Concepts containing current version NCI and current version SNOMEDCT atoms

SELECT DISTINCT 
    ca.concepts_id conceptId
FROM
    atoms a,
    atoms a1,
    concepts_atoms ca,
    concepts_atoms ca1,
    terminologies t,
    terminologies t1
WHERE
    ca.atoms_id = a.id
        AND ca1.atoms_id = a1.id
        AND a.terminology = 'NCI'
        AND a.terminology = t.terminology
        AND a.version = t.version
        AND t.current = true
        AND a1.terminology = 'SNOMEDCT_US'
        AND a1.terminology = t1.terminology
        AND a1.version = t1.version
        AND t1.current = true        
        AND ca.concepts_id = ca1.concepts_id

ambig_no_ncimth_pn: Ambiguous concepts where at least one has no PN (sepstring)
```
Ambiguous?
```
ambig_no_mth_pn: Ambiguous concepts where at least one has no PN (sepstring)
ambig_no_rel: Ambiguous concepts where at least one pair lacks an approved REL
pn_pn_ambig: Identical (same SUI) PN's in multiple concepts

multiple_pn: Concepts with multiple MTH/PN atoms

SELECT DISTINCT
    ca.concepts_id conceptId
FROM
    atoms a,
    concepts_atoms ca
WHERE
    ca.atoms_id = a.id
        AND a.terminology = 'MTH'
        AND a.termType = 'PN'
GROUP BY ca.concepts_id
HAVING COUNT(ca.concepts_id) > 1

pn_no_ambig: Concept has MTH/PN atom but no ambiguous string
ambig_pn: MTH/PN atom is ambiguous but has no matching ambiguous string
pn_orphan: MTH/PNs on their own

nosty: No STY

SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
        LEFT JOIN
    concepts_semantic_type_components cs ON (c.id = cs.concepts_id)
WHERE
    cs.semanticTypes_id IS NULL

missing_sty: Reviewed concepts without releasable Semantic Types

-- Is 'Reviewed' =  workflowStatus.READY_FOR_PUBLICATION?  How about PUBLISHED?
 
SELECT DISTINCT
    c.id conceptId
FROM
    concepts c
WHERE
    c.workflowStatus = 'READY_FOR_PUBLICATION'
        AND NOT c.id IN (SELECT 
            c.id
        FROM
            concepts c,
            concepts_semantic_type_components cs,
            semantic_type_components s
        WHERE
            c.id = cs.concepts_id
                AND cs.semanticTypes_id = s.id
                AND s.publishable = TRUE)

cbo_chem: Current version CBO concepts with CHEM STYs

-- to find chemical semantic types
(select distinct semanticTypeCategoryMap sty
from projects a, ProjectJpa_semanticTypeCategoryMap b
where a.id = b.ProjectJpa_id
  and semanticTypeCategoryMap_KEY = 'chemical'
  and a.terminology = :terminology);

go_chem: Current version GO concepts with CHEM STYs

--Not sure about final line.
 
SELECT DISTINCT
    c.id conceptId
FROM
    concepts c,
    concepts_semantic_type_components cs,
    semantic_type_components s,
    terminologies t
WHERE
    c.terminology = 'GO'
        AND c.terminology = t.terminology
        AND c.version = t.version
        AND t.current = true
        AND c.id = cs.concepts_id
        AND cs.semanticTypes_id = s.id
        AND s.semanticType LIKE 'Chem%'

mdr_chem: Current version MDR concepts with CHEM STYs
- See above

true_orphan: Concepts with no releaseable relationships to any other concept

SELECT 
    c.id conceptId
FROM
    concepts c
WHERE
    NOT c.id IN (SELECT 
            c.id
        FROM
            concepts c,
            concept_relationships cr
        WHERE
            cr.from_id = c.id
                AND cr.publishable = TRUE)

deleted_cui_split: Complex split/merge case CUIs that are going away - will need bequeathal
split_demotions: Demotions overlapping with a bad merge that was split
nci_pt_mrg: Merged current version NCI/PT atoms
mxsuppr: Concepts with identical LUI atoms that have mixed suppressibility

.~ Mixed status concepts 196 Sat Jan 7 09:19:47 2017
~ Suppressible preferred name (level 0,9) 0 Sat Jan 7 09:19:58 2017