Ad-hoc Query: NIH Trials with No Special Category
-------------------------------------------------

Old Version
===========
select distinct (q.doc_id) AS "CDR ID", q1.value AS "Current Protocol Status"
FROM query_Term q,
           query_term q1,
           docs_with_pub_status d
WHERE q.doc_id=q1.doc_id
AND      q.doc_id=d.doc_id
AND   q.path LIKE  '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteID/@cdr:ref'
AND  q.int_val /*=32457*/ in (32457, 34517)
AND  q1.path LIKE '/InScopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
AND q1.value IN  ('Active', 'Approved-not yet active', 'Temporarily closed')
AND d.pv='Y'
AND q.doc_id NOT IN
(SELECT DISTINCT doc_id from query_Term
WHERE path like '/InScopeProtocol/ProtocolSpecialCategory/SpecialCategory')
AND q.doc_id NOT IN
(SELECT DISTINCT org_site.doc_id
   FROM query_term org_site
   JOIN query_term site_status
     ON site_status.doc_id = org_site.doc_id
    AND LEFT(site_status.node_loc, 16) = LEFT(org_site.node_loc, 16)
  WHERE site_status.path = '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteStatus'
    AND org_site.path = '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/ProtocolSites/OrgSite/OrgSiteID/@cdr:ref'
    AND org_site.int_val IN (32457, 34517)
    AND site_status.value = 'Closed')



New Version
===========
SELECT DISTINCT (q.doc_id) AS "CDR ID", 
                 q1.value  AS "Current Protocol Status"
  FROM query_Term q
  JOIN query_term q1
    ON q.doc_id = q1.doc_id
   AND (q1.path = '/CTGovProtocol/OverallStatus'
        OR
        q1.path = '/InScopeProtocol/ProtocolAdminInfo' +
                  '/CurrentProtocolStatus'
       )
   AND q1.value IN ('Active', 'Approved-not yet active', 
                    'Temporarily closed')
  JOIN docs_with_pub_status d
    ON q.doc_id = d.doc_id
 WHERE q.int_val in (32457, 34517)
   AND d.pv = 'Y'
   AND (q.path  = '/CTGovProtocol/Location/Facility' +
                  '/Name/@cdr:ref'
        OR
        q.path  = '/InScopeProtocol/ProtocolAdminInfo' +
                  '/ProtocolLeadOrg/ProtocolSites' +
                  '/OrgSite/OrgSiteID/@cdr:ref'
       )
   AND q.doc_id NOT IN
       (SELECT DISTINCT doc_id 
          FROM query_Term
         WHERE (path like '/CTGovProtocol/PDQAdminInfo' +
                          '/ProtocolSpecialCategory' +
                          '/SpecialCategory'
                OR
                path like '/InScopeProtocol' +
                          '/ProtocolSpecialCategory' +
                          '/SpecialCategory'
               )
       )
   AND q.doc_id NOT IN
       (SELECT DISTINCT org_site.doc_id
          FROM query_term org_site
          JOIN query_term site_status
            ON site_status.doc_id = org_site.doc_id
           AND LEFT(site_status.node_loc, 16) = LEFT(org_site.node_loc, 16)
         WHERE site_status.path = '/InScopeProtocol/ProtocolAdminInfo' +
                                  '/ProtocolLeadOrg/ProtocolSites' +
                                  '/OrgSite/OrgSiteStatus'
           AND org_site.path    = '/InScopeProtocol/ProtocolAdminInfo' +
                                  '/ProtocolLeadOrg/ProtocolSites' +
                                  '/OrgSite/OrgSiteID/@cdr:ref'
           AND org_site.int_val IN (32457, 34517)
           AND site_status.value = 'Closed'
       )
   AND q.doc_id NOT IN
       (SELECT DISTINCT org_site.doc_id
          FROM query_term org_site
          JOIN query_term site_status
            ON site_status.doc_id = org_site.doc_id
           AND LEFT(site_status.node_loc, 8) = LEFT(org_site.node_loc, 8)
         WHERE org_site.int_val IN (32457, 34517)
           AND org_site.path = '/CTGovProtocol/Location' +
                               '/Facility/Name/@cdr:ref'
           -- AND site_status.path = '/CTGovProtocol/Location/Status'
           -- AND site_status.value = 'Closed'
       )