3 Replies Latest reply: Dec 15, 2010 8:44 PM by 174793 RSS

    How to get a count of total data points entered?

    793174
      What Oracle tables are involved in getting the total number of data points actually entered in a particular DCM or panel.
      I want to create a sql statement to show: Study, Panel, Total actual data points entered?
        • 1. Re: How to get a count of total data points entered?
          793174
          This is what I have so far...

          SELECT CS.STUDY,
          DC.NAME,
          COUNT(RP.RESPONSE_ID) "TOTAL DATA POINTS"

          FROM RESPONSES RP

          INNER JOIN CLINICAL_STUDIES CS ON ( RP.CLINICAL_STUDY_ID = CS.CLINICAL_STUDY_ID)

          INNER JOIN RECEIVED_DCMS RD ON ( RP.RECEIVED_DCM_ID = RD.RECEIVED_DCM_ID
          AND RP.CLINICAL_STUDY_ID = RD.CLINICAL_STUDY_ID
          AND RD.RECEIVED_DCM_STATUS_CODE <> 'REMOVED')

          INNER JOIN DCMS DC ON ( RD.DCM_ID = DC.DCM_ID
          AND RD.DCM_SUBSET_SN = DC.DCM_SUBSET_SN
          AND RD.DCM_LAYOUT_SN = DC.DCM_LAYOUT_SN
          AND RD.CLINICAL_STUDY_ID = DC.CLINICAL_STUDY_ID)

          INNER JOIN DCM_QUESTIONS DQ ON ( RP.DCM_QUESTION_ID = DQ.DCM_QUESTION_ID
          AND RD.DCM_ID = DQ.DCM_ID
          AND RD.DCM_SUBSET_SN = DQ.DCM_QUE_DCM_SUBSET_SN
          AND RD.DCM_LAYOUT_SN = DQ.DCM_QUE_DCM_LAYOUT_SN
          AND RD.CLINICAL_STUDY_ID = DQ.CLINICAL_STUDY_ID
          AND DQ.ENTERABLE_FLAG = 'Y')


          WHERE (RP.VALUE_TEXT IS NOT NULL OR RP.EXCEPTION_VALUE_TEXT IS NOT NULL)

          GROUP BY CS.STUDY,
          DC.NAME

          ORDER BY CS.STUDY,
          DC.NAME ;
          • 2. Re: How to get a count of total data points entered?
            793174
            This was the correct solution.
            • 3. Re: How to get a count of total data points entered?
              174793
              Your DCM QuestionGroups are missing from the script.