Ad-hoc Query: Inactive NCI Intramural trials with Special Category
------------------------------------------------------------------

Old Version
===========
select distinct q.doc_id, d.title
from document d, 
     query_term q,
     query_term q1,
     query_term q2,
     query_term q3
where q.path = '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteID/@cdr:ref'
and q1.path = '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteStatus'
and q2.path = '/InScopeProtocol/ProtocolSpecialCategory/SpecialCategory'
and q3.path = '/InscopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
and q2.value = 'NIH Clinical Center trial'
and q.int_val in (32457, 34517)
and q.doc_id = q1.doc_id
and q2.doc_id = q.doc_id
and q3.doc_id = q.doc_id
and d.id = q.doc_id
and q1.value <> 'Active'
and q3.value = 'Active'
and LEFT(q.node_loc, 16) = LEFT(q1.node_loc, 16)
order by q.doc_id


New Version
===========
a) InScopeProtocols

SELECT DISTINCT q.doc_id, d.title
  FROM document d
   JOIN query_term q
    ON q.doc_id = d.id
  JOIN query_term q1
    ON q.doc_id = q1.doc_id
   AND q1.path  = '/InScopeProtocol/ProtocolAdminInfo' +
                  '/ProtocolLeadOrg/ProtocolSites'     +
                  '/OrgSite/OrgSiteStatus'
  JOIN query_term q2
    ON q.doc_id = q2.doc_id
   AND q2.path  = '/InScopeProtocol'                   +
                  '/ProtocolSpecialCategory'           +
                  '/SpecialCategory'
  JOIN query_term q3
    ON q.doc_id = q3.doc_id
   AND q3.path  = '/InscopeProtocol/ProtocolAdminInfo' +
                  '/CurrentProtocolStatus'
 WHERE q.path   = '/InScopeProtocol/ProtocolAdminInfo' +
                  '/ProtocolLeadOrg/ProtocolSites'     +
                  '/OrgSite/OrgSiteID/@cdr:ref'
   AND q.int_val in (32457, 34517)
   AND q1.value <> 'Active'
   AND q3.value = 'Active'
   AND LEFT(q.node_loc, 16) = LEFT(q1.node_loc, 16)
 ORDER BY q.doc_id


b) CTGovProtocols

SELECT DISTINCT q.doc_id, d.title
  FROM document d
  JOIN query_term q
    ON q.doc_id = d.id
  JOIN query_term q1
    ON q.doc_id = q1.doc_id
   AND q1.path = '/CTGovProtocol/Location/Status'
  JOIN query_term q2
    ON q.doc_id = q2.doc_id
   AND q2.path = '/CTGovProtocol/ProtocolSpecialCategory' +
                 '/SpecialCategory'
  JOIN query_term q3
    ON q.doc_id = q3.doc_id
   AND q3.path = '/CTGovProtocol/OverallStatus'
 WHERE q.path  = '/CTGovProtocol/Location' + 
                 '/Facility/Name/@cdr:ref'
   AND q.int_val in (32457, 34517)
   AND q1.value <> 'Active'
   AND q3.value = 'Active'
   AND LEFT(q.node_loc, 8) = LEFT(q1.node_loc, 8)
 ORDER BY q.doc_id