-- Query1: InScopeProtocols with no NCTID.
-- "Never registered in CTGOV".
SELECT distinct d.id
  FROM document d
  JOIN doc_type t
    ON d.doc_type = t.id
   AND t.name = 'InScopeProtocol'
  JOIN query_term q
    ON d.id = q.doc_id
 WHERE d.id NOT IN (
        SELECT doc_id
          FROM query_term
         WHERE path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
           AND value = 'ClinicalTrials.gov ID'
	)
 ORDER BY d.id



-- Query2: InScopeProtocols with an NCTID but are blocked from CTGOV.
-- "Blocked from CTGOV".
SELECT qIdType.doc_id
  FROM query_term qIdType
  JOIN query_term qBlocked
    ON qIdType.doc_id = qBlocked.doc_id
 WHERE qIdType.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
   AND qIdType.value = 'ClinicalTrials.gov ID'
   AND qBlocked.path = '/InScopeProtocol/BlockedFromCTGov'
 ORDER BY qIdType.doc_id



-- Query3: InScopeProtocols with an NCTID and are Completed before 2007-09-27.
-- "Completed prior to Sept 27, 2007".
SELECT qCurStat.doc_id
  FROM query_term qCurStat
  JOIN query_term qIdType
    ON qCurStat.doc_id = qIdType.doc_id
   AND qIdType.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
   AND qIdType.value = 'ClinicalTrials.gov ID'
  JOIN query_term qLeadOrg
    ON qCurStat.doc_id = qLeadOrg.doc_id
   AND qLeadOrg.path =
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgRole'
   AND qLeadOrg.value = 'Primary'
  JOIN query_term qOrgStat
    ON qCurStat.doc_id = qOrgStat.doc_id
   AND qOrgStat.path = 
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgProtocolStatuses/CurrentOrgStatus/StatusName'
   AND qOrgStat.value = 'Completed'
   AND LEFT(qOrgStat.node_loc, 8) = LEFT(qLeadOrg.node_loc, 8)
  JOIN query_term qStatDt
    ON qCurStat.doc_id = qStatDt.doc_id
   AND qStatDt.path =
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgProtocolStatuses/CurrentOrgStatus/StatusDate'
   AND qStatDt.value < '2007-09-27'
   AND LEFT(qStatDt.node_loc, 8) = LEFT(qLeadOrg.node_loc, 8)
 WHERE qCurStat.path =
    '/InScopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
   AND qCurStat.value = 'Completed'
 ORDER BY qCurStat.doc_id



-- Query4: InScopeProtocols with NCTID that were withdrawn from PDQ.
-- "Withdrawn from PDQ".
SELECT qCurStat.doc_id
  FROM query_term qCurStat
  JOIN query_term qIdType
    ON qCurStat.doc_id = qIdType.doc_id
   AND qIdType.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
   AND qIdType.value = 'ClinicalTrials.gov ID'
  WHERE qCurStat.path =
    '/InScopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
   AND qCurStat.value = 'Withdrawn from PDQ'
 ORDER BY qCurStat.doc_id



-- Query5: InScopeProtocols withdrawn before 2007-09-27.
-- Identical to Query3 except both status="Withdrawn" instead of "Completed"
-- "Withdrawn prior to Sept 27, 2007".
SELECT qCurStat.doc_id
  FROM query_term qCurStat
  JOIN query_term qIdType
    ON qCurStat.doc_id = qIdType.doc_id
   AND qIdType.path = '/InScopeProtocol/ProtocolIDs/OtherID/IDType'
   AND qIdType.value = 'ClinicalTrials.gov ID'
  JOIN query_term qLeadOrg
    ON qCurStat.doc_id = qLeadOrg.doc_id
   AND qLeadOrg.path =
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgRole'
   AND qLeadOrg.value = 'Primary'
  JOIN query_term qOrgStat
    ON qCurStat.doc_id = qOrgStat.doc_id
   AND qOrgStat.path = 
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgProtocolStatuses/CurrentOrgStatus/StatusName'
   AND qOrgStat.value = 'Withdrawn'
   AND LEFT(qOrgStat.node_loc, 8) = LEFT(qLeadOrg.node_loc, 8)
  JOIN query_term qStatDt
    ON qCurStat.doc_id = qStatDt.doc_id
   AND qStatDt.path =
    '/InScopeProtocol/ProtocolAdminInfo/ProtocolLeadOrg/LeadOrgProtocolStatuses/CurrentOrgStatus/StatusDate'
   AND qStatDt.value < '2007-09-27'
   AND LEFT(qStatDt.node_loc, 8) = LEFT(qLeadOrg.node_loc, 8)
 WHERE qCurStat.path =
    '/InScopeProtocol/ProtocolAdminInfo/CurrentProtocolStatus'
   AND qCurStat.value = 'Withdrawn'
 ORDER BY qCurStat.doc_id
