This discussion is archived
3 Replies Latest reply: Dec 15, 2010 6:44 PM by 174793 RSS

How to get a count of total data points entered?

793174 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    This was the correct solution.
  • 3. Re: How to get a count of total data points entered?
    174793 Newbie
    Currently Being Moderated
    Your DCM QuestionGroups are missing from the script.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points