0 Replies Latest reply: Sep 22, 2010 6:47 PM by 799680 RSS

    Generating A CRF Completion Report

    799680
      Hi,

      I am in the process of creating a CRF completion report, by site, patient and visit. The problem I have with my current SQL is that it doesn't pull in records for DCIS I am expectiing but there is no data yet. Here is my SQL:

      I thought the outer join statment 'a.dci_id = c.dci_id(+)' would give this to me but it isn't :( Any help would be appreciate!

      SELECT c.domain, a.site_id, trim(a.patient), trim(c.SHORT_NAME) AS dci_name, trim(a.visit_number), trim(a.clin_plan_eve_name) AS visit_name,
      trim(a.subevent_number), trim(a.data_lock_flag), trim(a.received_dcm_status_code) as status, trim(a.document_number), trim(a.blank_flag), rxc.rdc.rdci_verify_status ('P', a.received_dci_id) AS verify_status,
      rxc.rdc.rdci_app_status ('P', a.received_dci_id) as approve_status, (b.n) AS number_of_discrepancies
      FROM rxc.received_dcms a,
      (SELECT discrepancy_rev_status_code, received_dcm_id, COUNT (*) AS n
      FROM rxc.discrepancy_entries a
      WHERE discrepancy_rev_status_code LIKE '%REV%'
      GROUP BY discrepancy_rev_status_code, received_dcm_id) b,
      rxc.dcis c
      WHERE received_dcm_status_code != 'REMOVED'
      AND a.received_dcm_id = b.received_dcm_id(+)
      AND a.dci_id = c.dci_id(+)
      ORDER BY patient, visit_number, subevent_number

      I modified the above statement from a post in OHSUG.

      and

      SELECT "OCL_STUDY_SITES"."STUDY_SITE", "OCL_INVESTIGATORS"."LAST_NAME", "OCL_STUDIES"."TITLE", "OCL_STUDY_SITES"."SITE_ID", "OCL_STUDIES"."STUDY"
      FROM ("RXA_DES"."OCL_STUDY_SITES" "OCL_STUDY_SITES" CROSS JOIN "RXA_DES"."OCL_STUDIES" "OCL_STUDIES") INNER JOIN ("RXA_DES"."OCL_INVESTIGATORS" "OCL_INVESTIGATORS" INNER JOIN "RXA_DES"."OCL_STUDY_SITE_ROLES" "OCL_STUDY_SITE_ROLES" ON "OCL_INVESTIGATORS"."INVESTIGATOR_ID"="OCL_STUDY_SITE_ROLES"."INVESTIGATOR_ID") ON "OCL_STUDY_SITES"."SITE_ID"="OCL_STUDY_SITE_ROLES"."SITE_ID"
      ORDER BY "OCL_STUDY_SITES"."STUDY_SITE"