Page tree
Skip to end of metadata
Go to start of metadata

This report shows the mean score (the average results of the rating scale   questions) for each follow-up survey, survey page, and survey question.

 

Report NameFollow-Up Evaluation
REPORT_NAME_IDFollowUpEvaluation
REPORT_ID123
CATEGORY_IDLearning
GROUP_IDFollowUpEvaluation
WORKFLOW_IDRun FollowUpEvaluation Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

 

MeanScoreForSurveySection
SELECT ( CASE 
           WHEN 'Retraining' = ? THEN (SELECT ( CASE 
                                                  WHEN 
                                      (SELECT Count(DISTINCT( 
                                              rating_scale_id ) 
                                              ) AS 
                                              V_RATING_SETS 
                                       FROM 
                                                  pa_survey_question 
                                                        WHERE  section_id IN ( 
                                                               SELECT 
                                                               section_id 
                                                               FROM 
                                                               pa_survey_section 
                                                               WHERE 
                                                               survey_id = ? 
                                                               AND section_id = 
                                                                   ?) 
                                                               AND question_type 
                                                                   = 
                                      'RATING SCALE' 
                                      ) = 1 
                                                THEN 
                                                  (SELECT 
         Decode(Round(SUM(PSR.stud_response_value) / Count(*), 1), NULL, 
         -1, 
         Round(SUM(PSR.stud_response_value) / Count(*), 1)) 
         FROM   pa_survey_responses PSR, 
         pa_stud_survey PSS, 
         pa_cpnt_survey PCS, 
         pa_survey_question PSQ, 
         pa_ssg_inst PSI, 
         pa_ssg_locn PSL 
         WHERE  PSS.cpnt_survey_id = PCS.cpnt_survey_id 
         AND PSR.stud_survey_id = PSS.stud_survey_id 
         AND PSR.question_id = PSQ.question_id 
         AND PSS.survey_status_id = 'COMPLETED' 
         AND PCS.survey_id = ? 
         AND PSQ.question_type = 'RATING SCALE' 
         AND PSR.stud_response_value <> 0 
         AND PSS.sch_id = PSI.schd_id(+) 
         AND PSS.sch_id = PSL.schd_id(+) 
         AND PCS.cpnt_survey_id = ? 
         AND PSQ.section_id = ? 
         AND PSS.item_completion_date <> (SELECT Min(item_completion_date) 
                                       FROM   pa_stud_survey SS, 
                                              pa_questionnaire_survey QS, 
                                              pa_cpnt_survey CS 
                                       WHERE 
          SS.cpnt_survey_id = PSS.cpnt_survey_id 
          AND SS.stud_id = PSS.stud_id 
          AND CS.survey_id = QS.survey_id 
          AND SS.cpnt_survey_id = 
              CS.cpnt_survey_id 
          AND QS.level_indicator = 3) 
         /**           and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]           and PSS.STUD_ID IN [UserSearch]           and PSS.SCH_ID IN [ScheduledOfferingSearch]           and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]           and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]           and PSI.INST_ID IN [InstructorSearch]           and PSL.LOCN_ID IN [LocationSearch]           and PCS.IS_ACTIVE = [IncludePreviousItemAssociations]           */ 
         ) 
         ELSE -1 
         END ) AS MEAN_SCORE 
          FROM   dual) 
           ELSE (SELECT ( CASE 
                            WHEN (SELECT Count(DISTINCT( rating_scale_id )) AS 
                                         V_RATING_SETS 
                                  FROM   pa_survey_question 
                                  WHERE  section_id IN (SELECT section_id 
                                                        FROM   pa_survey_section 
                                                        WHERE  survey_id = ? 
                                                               AND section_id = 
                                                                   ?) 
                                         AND question_type = 'RATING SCALE') = 1 
                          THEN 
                            (SELECT 
                Decode(Round(SUM(PSR.stud_response_value) / Count(*), 1), NULL, 
                -1, 
                Round(SUM(PSR.stud_response_value) / Count(*), 1)) 
                FROM   pa_survey_responses PSR, 
                pa_stud_survey PSS, 
                pa_cpnt_survey PCS, 
                pa_survey_question PSQ, 
                pa_ssg_inst PSI, 
                pa_ssg_locn PSL 
                WHERE  PSS.cpnt_survey_id = PCS.cpnt_survey_id 
                AND PSR.stud_survey_id = PSS.stud_survey_id 
                AND PSR.question_id = PSQ.question_id 
                AND PSS.survey_status_id = 'COMPLETED' 
                AND PCS.survey_id = ? 
                AND PSQ.question_type = 'RATING SCALE' 
                AND PSR.stud_response_value <> 0 
                AND PSS.sch_id = PSI.schd_id(+) 
                AND PSS.sch_id = PSL.schd_id(+) 
                AND PCS.cpnt_survey_id = ? 
                AND PSQ.section_id = ? 
                AND PSS.item_completion_date = (SELECT Min(item_completion_date) 
                                             FROM   pa_stud_survey SS, 
                                                    pa_questionnaire_survey QS, 
                                                    pa_cpnt_survey CS 
                                             WHERE 
                 SS.cpnt_survey_id = PSS.cpnt_survey_id 
                 AND SS.stud_id = PSS.stud_id 
                 AND CS.survey_id = QS.survey_id 
                 AND SS.cpnt_survey_id = CS.cpnt_survey_id 
                 AND QS.level_indicator = 3) 
                /**                  and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]                  and PSS.STUD_ID IN [UserSearch]                  and PSS.SCH_ID IN [ScheduledOfferingSearch]                  and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                  and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]                  and PSI.INST_ID IN [InstructorSearch]                  and PSL.LOCN_ID IN [LocationSearch]                  and PCS.IS_ACTIVE = [IncludePreviousItemAssociations]                  */ 
                ) 
                ELSE -1 
                END ) AS MEAN_SCORE 
                 FROM   dual) 
         END ) AS MEAN_SCORE 
FROM   dual 
PageAndScale
SELECT PAGE_DETAILS.page_title, 
       T1.mean_score PRE_TEST_MEAN_SCORE, 
       T2.mean_score POST_TEST_MEAN_SCORE 
FROM   (SELECT QS.survey_id, 
               CS.cpnt_survey_id, 
               SQ.section_id, 
               SSEC.section_order, 
               Concat('Page ', SSEC.section_order) PAGE_TITLE 
        FROM   pa_questionnaire_survey QS 
               left outer join pa_cpnt_survey CS 
                            ON QS.survey_id = CS.survey_id 
               right outer join pa_stud_survey SS 
                             ON CS.cpnt_survey_id = SS.cpnt_survey_id 
               left outer join pa_survey_responses SR 
                            ON SS.stud_survey_id = SR.stud_survey_id 
               left outer join pa_survey_question SQ 
                            ON SR.question_id = SQ.question_id 
               left outer join pa_survey_section SSEC 
                            ON SQ.section_id = SSEC.section_id 
        WHERE  SQ.section_id IS NOT NULL 
               AND SS.survey_status_id = 'COMPLETED' 
               AND QS.level_indicator = 3 
               AND QS.survey_id LIKE ? 
               AND CS.cpnt_survey_id = ? 
        GROUP  BY QS.survey_id, 
                  CS.cpnt_survey_id, 
                  SQ.section_id, 
                  SSEC.section_order 
        ORDER  BY SSEC.section_order) PAGE_DETAILS, 
       (SELECT PCS.survey_id, 
               PCS.cpnt_survey_id, 
               PSQ.section_id, 
               Count(DISTINCT( rating_scale_id )) rating_scales, 
               ( CASE 
                   WHEN ( Count(DISTINCT( rating_scale_id )) ) = 1 THEN ( 
                   Round(SUM(PSR.stud_response_value) / Count(*), 1) ) 
                   ELSE NULL 
                 END )                            AS MEAN_SCORE 
        FROM   pa_survey_responses PSR, 
               pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_survey_question PSQ, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PSS.cpnt_survey_id = PCS.cpnt_survey_id 
               AND PSR.stud_survey_id = PSS.stud_survey_id 
               AND PSR.question_id = PSQ.question_id 
               AND PSS.survey_status_id = 'COMPLETED' 
               AND PSQ.question_type = 'RATING SCALE' 
               AND PSR.stud_response_value <> 0 
               AND PSS.sch_id = PSI.schd_id(+) 
               AND PSS.sch_id = PSL.schd_id(+) 
               AND PSS.item_completion_date = (SELECT Min(item_completion_date) 
                                               FROM 
                   pa_stud_survey SS, 
                   pa_questionnaire_survey QS, 
                   pa_cpnt_survey 
                   CS 
                                WHERE 
                   SS.cpnt_survey_id = PSS.cpnt_survey_id 
                   AND SS.stud_id = PSS.stud_id 
                   AND CS.survey_id = QS.survey_id 
                   AND SS.cpnt_survey_id = CS.cpnt_survey_id 
                   AND QS.level_indicator = 3) 
        /**              and PSS.STUD_ID IN [UserSearch]              and PSS.SCH_ID IN [ScheduledOfferingSearch]              and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]              and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]              and PCS.IS_ACTIVE = [IncludePreviousItemAssociations]          */ 
        GROUP  BY PCS.survey_id, 
                  PCS.cpnt_survey_id, 
                  PSQ.section_id) T1, 
       (SELECT PCS.survey_id, 
               PCS.cpnt_survey_id, 
               PSQ.section_id, 
               Count(DISTINCT( rating_scale_id )) rating_scales, 
               ( CASE 
                   WHEN ( Count(DISTINCT( rating_scale_id )) ) = 1 THEN ( 
                   Round(SUM(PSR.stud_response_value) / Count(*), 1) ) 
                   ELSE NULL 
                 END )                            AS MEAN_SCORE 
        FROM   pa_survey_responses PSR, 
               pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_survey_question PSQ, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PSS.cpnt_survey_id = PCS.cpnt_survey_id 
               AND PSR.stud_survey_id = PSS.stud_survey_id 
               AND PSR.question_id = PSQ.question_id 
               AND PSS.survey_status_id = 'COMPLETED' 
               AND PSQ.question_type = 'RATING SCALE' 
               AND PSR.stud_response_value <> 0 
               AND PSS.sch_id = PSI.schd_id(+) 
               AND PSS.sch_id = PSL.schd_id(+) 
               AND PSS.item_completion_date <> (SELECT Min(item_completion_date) 
                                                FROM 
                   pa_stud_survey SS, 
                   pa_questionnaire_survey QS, 
                   pa_cpnt_survey CS 
                                                WHERE 
                   SS.cpnt_survey_id = PSS.cpnt_survey_id 
                   AND SS.stud_id = PSS.stud_id 
                   AND CS.survey_id = QS.survey_id 
                   AND SS.cpnt_survey_id = 
                       CS.cpnt_survey_id 
                   AND QS.level_indicator = 3) 
        /**              and PSS.STUD_ID IN [UserSearch]              and PSS.SCH_ID IN [ScheduledOfferingSearch]              and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]              and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]              and PCS.IS_ACTIVE = [IncludePreviousItemAssociations]          */ 
        GROUP  BY PCS.survey_id, 
                  PCS.cpnt_survey_id, 
                  PSQ.section_id) T2 
WHERE  PAGE_DETAILS.survey_id = T1.survey_id(+) 
       AND PAGE_DETAILS.survey_id = T2.survey_id(+) 
       AND PAGE_DETAILS.cpnt_survey_id = T1.cpnt_survey_id(+) 
       AND PAGE_DETAILS.cpnt_survey_id = T2.cpnt_survey_id(+) 
       AND PAGE_DETAILS.section_id = T1.section_id(+) 
       AND PAGE_DETAILS.section_id = T2.section_id(+) 
QuestionComments
SELECT DISTINCT PSR.stud_comments AS STUD_COMMENTS, 
                Concat(( INST.fname ), Concat(' ', Concat(( INST.lname ), Concat 
                                                   (' ', 
( INST.mi ))))) 
COMMENTS_INSTRUCTOR_NAME 
FROM   pa_cpnt_survey PCS, 
       pa_stud_survey PSS, 
       pa_survey_responses PSR, 
       pa_ssg_inst PSI, 
       pa_ssg_locn PSL, 
       pa_inst INST 
WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
       AND PSS.stud_survey_id = PSR.stud_survey_id 
       AND PSL.schd_id(+) = PSS.sch_id 
       AND PSI.schd_id(+) = PSS.sch_id 
       AND INST.inst_id(+) = PSR.inst_id 
       AND PSR.stud_comments IS NOT NULL 
       AND PSS.cpnt_survey_id = ? 
       AND PSR.question_id = ? 
       AND PSS.item_completion_date <> (SELECT Min(item_completion_date) 
                                        FROM   pa_stud_survey SS, 
                                               pa_questionnaire_survey QS, 
                                               pa_cpnt_survey CS 
                                        WHERE 
           SS.cpnt_survey_id = PSS.cpnt_survey_id 
           AND SS.stud_id = PSS.stud_id 
           AND CS.survey_id = QS.survey_id 
           AND SS.cpnt_survey_id = 
               CS.cpnt_survey_id 
           AND QS.level_indicator = 3) 
       AND 'Retraining' = ? 
/**          AND PCS.SURVEY_ID in [SurveyIDSearch]    AND (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) in [ItemSearch]    AND PCS.IS_ACTIVE  = [IncludePreviousItemAssociations]    AND PSS.STUD_ID in [UserSearch]    AND trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]    AND NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]    AND PSS.SCH_ID in [ScheduledOfferingSearch]    AND PSI.INST_ID in [InstructorSearch]    AND PSL.LOCN_ID in [LocationSearch]    */ 
UNION ALL 
SELECT DISTINCT PSR.stud_comments AS STUD_COMMENTS, 
                Concat(( INST.fname ), Concat(' ', Concat(( INST.lname ), Concat 
                                                   (' ', 
( INST.mi ))))) 
COMMENTS_INSTRUCTOR_NAME 
FROM   pa_cpnt_survey PCS, 
       pa_stud_survey PSS, 
       pa_survey_responses PSR, 
       pa_ssg_inst PSI, 
       pa_ssg_locn PSL, 
       pa_inst INST 
WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
       AND PSS.stud_survey_id = PSR.stud_survey_id 
       AND PSL.schd_id(+) = PSS.sch_id 
       AND PSI.schd_id(+) = PSS.sch_id 
       AND PSR.stud_comments IS NOT NULL 
       AND PSS.cpnt_survey_id = ? 
       AND PSR.question_id = ? 
       AND INST.inst_id(+) = PSR.inst_id 
       AND PSS.item_completion_date = (SELECT Min(item_completion_date) 
                                       FROM   pa_stud_survey SS, 
                                              pa_questionnaire_survey QS, 
                                              pa_cpnt_survey CS 
                                       WHERE 
           SS.cpnt_survey_id = PSS.cpnt_survey_id 
           AND SS.stud_id = PSS.stud_id 
           AND CS.survey_id = QS.survey_id 
           AND SS.cpnt_survey_id = CS.cpnt_survey_id 
           AND QS.level_indicator = 3) 
       AND 'First Time Item Completion' = ? 
/**          AND PCS.SURVEY_ID in [SurveyIDSearch]    AND (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) in [ItemSearch]    AND PCS.IS_ACTIVE  = [IncludePreviousItemAssociations]    AND PSS.STUD_ID in [UserSearch]    AND trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]    AND NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]    AND PSS.SCH_ID in [ScheduledOfferingSearch]    AND PSI.INST_ID in [InstructorSearch]    AND PSL.LOCN_ID in [LocationSearch]    */ 
SurveyByItem
SELECT 'First Time Item Completion' 
       ASSESSMENT_TYPE, 
       QS.survey_id, 
       CS.cpnt_survey_id, 
       CPNT.cpnt_title, 
       SQ.section_id, 
       SQ.question_id, 
       Decode(SR.stud_response_value, NULL, SR.stud_response_text, 
                                      SR.stud_response_value) 
       ANSWER_ID, 
       QS.name, 
       CS.cpnt_typ_id, 
       CS.cpnt_id, 
       CS.rev_dte, 
       SSEC.section_order, 
       SSEC.section_title, 
       SSEC.resource_type, 
       SQ.question_order, 
       SQ.question_type, 
       SQ.question_text, 
       Decode(SQ.question_type, 'RATING SCALE', (SELECT 
       CONCAT(CONCAT(pa_rating_scale_label.rating, ' - '), al.label_value) 
                                                 FROM   pa_rating_scale_label, 
       pv_i18n_active_locale_label al 
                                                 WHERE 
       pa_rating_scale_label.rating = SR.stud_response_value 
       AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id 
       AND al.label_id (+) = pa_rating_scale_label.rating_label 
       AND al.locale_id (+) = ?), 
                                'OPEN ENDED', Max(SR.stud_response_text), 
                                Max(QAC.choice_label)) 
       ANSWER, 
       COUNT(DISTINCT SR.response_id) 
       ANSWER_COUNT, 
       CONCAT(( INSTDETAIL.fname ), CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                                                CONCAT(' ', 
       ( INSTDETAIL.mi ))))) 
       INSTRUCTOR_NAME, 
       Round(Decode(COUNT(DISTINCT SR.response_id), 0, 0, 
                                                    ( 
                   COUNT(DISTINCT SR.response_id) / (SELECT 
                   COUNT(DISTINCT PSS1.stud_survey_id) 
                                                     FROM 
                   pa_cpnt_survey PCS1, 
                   pa_stud_survey PSS1, 
                   pa_survey_responses PSR1, 
                   pa_ssg_inst PSI1, 
                   pa_ssg_locn PSL1 
                                                     WHERE 
                         PCS1.cpnt_survey_id = PSS1.cpnt_survey_id 
                         AND PSS1.stud_survey_id = PSR1.stud_survey_id 
                         AND PSL1.schd_id(+) = PSS1.sch_id 
                         AND PSI1.schd_id(+) = PSS1.sch_id 
                         AND PSS1.cpnt_survey_id = CS.cpnt_survey_id 
                         AND PSR1.question_id = SQ.question_id 
                         AND PSS1.survey_status_id = 'COMPLETED' 
                         AND PSS1.item_completion_date = 
                             (SELECT Min(item_completion_date) 
                              FROM 
                             pa_stud_survey PSS2, 
                             pa_questionnaire_survey PQS2, 
                             pa_cpnt_survey PCS2 
                                                          WHERE 
                             PSS2.cpnt_survey_id = PSS1.cpnt_survey_id 
                             AND PSS2.stud_id = PSS1.stud_id 
                             AND PCS2.survey_id = PQS2.survey_id 
                             AND 
                                 PSS2.cpnt_survey_id = PCS2.cpnt_survey_id 
                                                                 AND 
                             PQS2.level_indicator = 3 
                                                         ) 
                                                    /** AND PCS1.SURVEY_ID in [SurveyIDSearch]                                                          AND (PCS1.CPNT_TYP_ID,PCS1.CPNT_ID,PCS1.REV_DTE) in [ItemSearch]                                                          AND PCS1.IS_ACTIVE  = [IncludePreviousItemAssociations]                                                          AND PSS1.STUD_ID in [UserSearch]                                                           AND trunc(PSS1.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                                                          AND NOT trunc(PSS1.ITEM_COMPLETION_DATE) > [LearningEventToDate]                                                         AND PSS1.SCH_ID in [ScheduledOfferingSearch]                                                          AND PSI1.INST_ID in [InstructorSearch]                                                          AND PSL1.LOCN_ID in [LocationSearch]                                                          */ 
                                                    ) * 100 )), 1) 
       ANSWER_PERCENTAGE, 
       --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.survey_id = QS.survey_id ) TOTAL_SURVEY,
       (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
        FROM   pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
               AND PSL.schd_id(+) = PSS.sch_id 
               AND PSI.schd_id(+) = PSS.sch_id 
               AND PCS.survey_id = QS.survey_id 
       /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]         and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate] and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]        and PSI.INST_ID IN [InstructorSearch]and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations] */)                                                                 TOTAL_SURVEY,
       --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.survey_id = QS.survey_id and pss.survey_status_id = 'COMPLETED') COMPLETED_SURVEY,
       (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
        FROM   pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
               AND PSS.survey_status_id = 'COMPLETED' 
               AND PSL.schd_id(+) = PSS.sch_id 
               AND PSI.schd_id(+) = PSS.sch_id 
               AND PCS.survey_id = QS.survey_id 
       /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]        and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]*/) COMPLETED_SURVEY,
       --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.CPNT_SURVEY_ID = CS.CPNT_SURVEY_ID) TOTAL_ITEM_SURVEY,
       (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
        FROM   pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
               AND PSL.schd_id(+) = PSS.sch_id 
               AND PSI.schd_id(+) = PSS.sch_id 
               AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
       /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]         and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate] and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]        and PSI.INST_ID IN [InstructorSearch]and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations] */)
       TOTAL_ITEM_SURVEY, 
       --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.CPNT_SURVEY_ID = CS.CPNT_SURVEY_ID and pss.survey_status_id = 'COMPLETED') COMPLETED_ITEM_SURVEY,
       (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
        FROM   pa_stud_survey PSS, 
               pa_cpnt_survey PCS, 
               pa_ssg_inst PSI, 
               pa_ssg_locn PSL 
        WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
               AND PSS.survey_status_id = 'COMPLETED' 
               AND PSL.schd_id(+) = PSS.sch_id 
               AND PSI.schd_id(+) = PSS.sch_id 
               AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
       /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]        and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]*/) COMPLETED_ITEM_SURVEY,
       --( SELECT COUNT(*) AS OFFERINGS FROM PA_CPNT_SURVEY PCS,PA_STUD_SURVEY PSS WHERE PCS.cpnt_survey_id=pss.cpnt_survey_id and pss.sch_id is not null and PCS.cpnt_survey_id = CS.CPNT_SURVEY_ID) SCHDULE_OFFERING    
       (SELECT COUNT(DISTINCT sch_id) 
        FROM   pa_stud_survey 
        WHERE  sch_id IS NOT NULL 
               AND cpnt_survey_id IN (SELECT PCS.cpnt_survey_id 
                                      FROM   pa_cpnt_survey PCS, 
                                             pa_stud_survey PSS, 
                                             pa_ssg_inst PSI, 
                                             pa_ssg_locn PSL 
                                      WHERE 
                   PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                   AND PSS.sch_id IS NOT NULL 
                   AND PSL.schd_id(+) = PSS.sch_id 
                   AND PSI.schd_id(+) = PSS.sch_id 
                   AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
                                     /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                                      and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]                                      */ 
                                      GROUP  BY ( PCS.cpnt_survey_id )) 
       /** and SCH_ID IN [ScheduledOfferingSearch]*/) 
       SCHDULE_OFFERING 
FROM   pa_questionnaire_survey QS 
       LEFT OUTER JOIN pa_cpnt_survey CS 
                    ON QS.survey_id = CS.survey_id 
       RIGHT OUTER JOIN pa_stud_survey SS 
                     ON CS.cpnt_survey_id = SS.cpnt_survey_id 
       LEFT OUTER JOIN pa_survey_responses SR 
                    ON SS.stud_survey_id = SR.stud_survey_id 
       LEFT OUTER JOIN pa_survey_question SQ 
                    ON SR.question_id = SQ.question_id 
       LEFT OUTER JOIN pa_survey_section SSEC 
                    ON SQ.section_id = SSEC.section_id 
       LEFT OUTER JOIN pa_question_answer_choices QAC 
                    ON QAC.question_id = SQ.question_id 
       LEFT OUTER JOIN pa_ssg_inst INSTRUCTOR 
                    ON INSTRUCTOR.schd_id = SS.sch_id 
       LEFT OUTER JOIN pa_ssg_locn LOCATION 
                    ON LOCATION.schd_id = SS.sch_id 
       LEFT OUTER JOIN pa_inst INSTDETAIL 
                    ON INSTDETAIL.inst_id = SR.inst_id, 
       --PA_QUESTION_ANSWER_CHOICES QAC, 
       pv_course CPNT--, 
--PA_SSG_INST INSTRUCTOR, 
--PA_SSG_LOCN LOCATION  
--AND ( ( SQ.QUESTION_TYPE = 'MULTIPLE CHOICE' OR SQ.QUESTION_TYPE = 'ONE CHOICE' ) AND ( QAC.ANSWER_ID = SR.STUD_RESPONSE_VALUE OR QAC.ANSWER_ID IS NULL OR SR.STUD_RESPONSE_VALUE IS NULL))
WHERE  ( QAC.answer_id = SR.stud_response_value 
          OR SR.stud_response_value IS NULL 
          OR SQ.question_type = 'RATING SCALE' ) 
       AND SQ.section_id IS NOT NULL 
       AND SS.survey_status_id = 'COMPLETED' 
       AND CS.cpnt_typ_id = CPNT.cpnt_typ_id 
       AND CS.cpnt_id = CPNT.cpnt_id 
       AND CS.rev_dte = CPNT.rev_dte 
       AND QS.level_indicator = 3 
       --AND ( SS.SCH_ID = INSTRUCTOR.SCHD_ID OR SS.SCH_ID IS NULL ) 
       --AND ( SS.SCH_ID = LOCATION.SCHD_ID OR SS.SCH_ID IS NULL )  
       AND SS.item_completion_date = (SELECT Min(item_completion_date) 
                                      FROM   pa_stud_survey pss, 
                                             pa_questionnaire_survey PQS, 
                                             pa_cpnt_survey PCS 
                                      WHERE 
           pss.cpnt_survey_id = SS.cpnt_survey_id 
           AND pss.stud_id = SS.stud_id 
           AND PCS.survey_id = PQS.survey_id 
           AND pss.cpnt_survey_id = 
               pcs.cpnt_survey_id 
           AND PQS.level_indicator = 3) 
/**             and QS.SURVEY_ID in [SurveyIDSearch]       and SS.STUD_ID in [UserSearch]       and trunc(SS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]       and NOT trunc(SS.ITEM_COMPLETION_DATE) > [LearningEventToDate]       and SS.SCH_ID in [ScheduledOfferingSearch]       AND INSTRUCTOR.INST_ID in [InstructorSearch]       AND LOCATION.LOCN_ID in [LocationSearch]       and QS.IS_ACTIVE  = [QuestionnaireSurveyStatus]       and CS.IS_ACTIVE  = [IncludePreviousItemAssociations]       and (CS.cpnt_typ_id,CS.cpnt_id,CS.rev_dte) in [ItemSearch]       and [security:PA_QUESTIONNAIRE_SURVEY QS]  */ 
GROUP  BY 'First Time Item Completion', 
          QS.survey_id, 
          CS.cpnt_survey_id, 
          CPNT.cpnt_title, 
          SQ.section_id, 
          SQ.question_id, 
          Decode(SR.stud_response_value, NULL, SR.stud_response_text, 
                                         SR.stud_response_value), 
          QS.name, 
          CS.cpnt_typ_id, 
          CS.cpnt_id, 
          CS.rev_dte, 
          SSEC.section_order, 
          SSEC.section_title, 
          SSEC.resource_type, 
          SQ.question_order, 
          SQ.question_type, 
          SQ.question_text, 
          SR.stud_response_text, 
          SR.stud_response_value, 
          SQ.rating_scale_id, 
          INSTDETAIL.fname, 
          INSTDETAIL.lname, 
          INSTDETAIL.mi 
UNION 
(SELECT 'Retraining' 
        ASSESSMENT_TYPE, 
        QS.survey_id, 
        CS.cpnt_survey_id, 
        CPNT.cpnt_title, 
        SQ.section_id, 
        SQ.question_id, 
        Decode(SR.stud_response_value, NULL, SR.stud_response_text, 
                                       SR.stud_response_value) 
        ANSWER_ID, 
        QS.name, 
        CS.cpnt_typ_id, 
        CS.cpnt_id, 
        CS.rev_dte, 
        SSEC.section_order, 
        SSEC.section_title, 
        SSEC.resource_type, 
        SQ.question_order, 
        SQ.question_type, 
        SQ.question_text, 
        Decode(SQ.question_type, 'RATING SCALE', (SELECT 
        CONCAT(CONCAT(pa_rating_scale_label.rating, ' - '), al.label_value) 
                                                  FROM   pa_rating_scale_label, 
        pv_i18n_active_locale_label al 
                                                  WHERE 
        pa_rating_scale_label.rating = SR.stud_response_value 
        AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id 
        AND al.label_id (+) = pa_rating_scale_label.rating_label 
        AND al.locale_id (+) = ?), 
                                 'OPEN ENDED', Max(SR.stud_response_text), 
                                 Max(QAC.choice_label)) 
        ANSWER, 
        COUNT(DISTINCT SR.response_id) 
        ANSWER_COUNT, 
        CONCAT(( INSTDETAIL.fname ), 
        CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                    CONCAT(' ', ( INSTDETAIL.mi ))))) 
        INSTRUCTOR_NAME, 
        Round(Decode(COUNT(DISTINCT SR.response_id), 0, 0, 
                                                     ( 
                    COUNT(DISTINCT SR.response_id) / (SELECT 
                    COUNT(DISTINCT PSS1.stud_survey_id) 
                                                      FROM 
                    pa_cpnt_survey PCS1, 
                    pa_stud_survey PSS1, 
                    pa_survey_responses PSR1, 
                    pa_ssg_inst PSI1, 
                    pa_ssg_locn PSL1 
                                                      WHERE 
                          PCS1.cpnt_survey_id = PSS1.cpnt_survey_id 
                          AND PSS1.stud_survey_id = PSR1.stud_survey_id 
                          AND PSL1.schd_id(+) = PSS1.sch_id 
                          AND PSI1.schd_id(+) = PSS1.sch_id 
                          AND PSS1.cpnt_survey_id = CS.cpnt_survey_id 
                          AND PSR1.question_id = SQ.question_id 
                          AND PSS1.survey_status_id = 'COMPLETED' 
                          AND PSS1.item_completion_date <> 
                              (SELECT Min(item_completion_date) 
                               FROM 
                              pa_stud_survey PSS2, 
                              pa_questionnaire_survey PQS2, 
                              pa_cpnt_survey PCS2 
                                                            WHERE 
                              PSS2.cpnt_survey_id = PSS1.cpnt_survey_id 
                              AND PSS2.stud_id = PSS1.stud_id 
                              AND PCS2.survey_id = PQS2.survey_id 
                              AND 
                                  PSS2.cpnt_survey_id = PCS2.cpnt_survey_id 
                                                                   AND 
                              PQS2.level_indicator = 3) 
                                                     /** AND PCS1.SURVEY_ID in [SurveyIDSearch]                                                           AND (PCS1.CPNT_TYP_ID,PCS1.CPNT_ID,PCS1.REV_DTE) in [ItemSearch]                                                           AND PCS1.IS_ACTIVE  = [IncludePreviousItemAssociations]                                                           AND PSS1.STUD_ID in [UserSearch]                                                            AND trunc(PSS1.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                                                           AND NOT trunc(PSS1.ITEM_COMPLETION_DATE) > [LearningEventToDate]                                                          AND PSS1.SCH_ID in [ScheduledOfferingSearch]                                                           AND PSI1.INST_ID in [InstructorSearch]                                                           AND PSL1.LOCN_ID in [LocationSearch]                                                           */ 
                                                     ) * 100 )), 1) 
        ANSWER_PERCENTAGE, 
        --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.survey_id = QS.survey_id ) TOTAL_SURVEY,
        (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
         FROM   pa_stud_survey PSS, 
                pa_cpnt_survey PCS, 
                pa_ssg_inst PSI, 
                pa_ssg_locn PSL 
         WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                AND PSL.schd_id(+) = PSS.sch_id 
                AND PSI.schd_id(+) = PSS.sch_id 
                AND PCS.survey_id = QS.survey_id 
        /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]          and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate] and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]         and PSI.INST_ID IN [InstructorSearch]and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations] */)                                                                 TOTAL_SURVEY,
        --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.survey_id = QS.survey_id and pss.survey_status_id = 'COMPLETED') COMPLETED_SURVEY,
        (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
         FROM   pa_stud_survey PSS, 
                pa_cpnt_survey PCS, 
                pa_ssg_inst PSI, 
                pa_ssg_locn PSL 
         WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                AND PSS.survey_status_id = 'COMPLETED' 
                AND PSL.schd_id(+) = PSS.sch_id 
                AND PSI.schd_id(+) = PSS.sch_id 
                AND PCS.survey_id = QS.survey_id 
        /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]         and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]*/) COMPLETED_SURVEY,
        --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.CPNT_SURVEY_ID = CS.CPNT_SURVEY_ID) TOTAL_ITEM_SURVEY,
        (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
         FROM   pa_stud_survey PSS, 
                pa_cpnt_survey PCS, 
                pa_ssg_inst PSI, 
                pa_ssg_locn PSL 
         WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                AND PSL.schd_id(+) = PSS.sch_id 
                AND PSI.schd_id(+) = PSS.sch_id 
                AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
        /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch]          and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate] and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]         and PSI.INST_ID IN [InstructorSearch]and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations] */)
        TOTAL_ITEM_SURVEY, 
        --( select count(*) as TotalSurveys from PA_STUD_SURVEY pss,PA_CPNT_SURVEY psc where psc.CPNT_SURVEY_ID = pss.CPNT_SURVEY_ID and psc.CPNT_SURVEY_ID = CS.CPNT_SURVEY_ID and pss.survey_status_id = 'COMPLETED') COMPLETED_ITEM_SURVEY,
        (SELECT COUNT(DISTINCT PSS.stud_survey_id) 
         FROM   pa_stud_survey PSS, 
                pa_cpnt_survey PCS, 
                pa_ssg_inst PSI, 
                pa_ssg_locn PSL 
         WHERE  PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                AND PSS.survey_status_id = 'COMPLETED' 
                AND PSL.schd_id(+) = PSS.sch_id 
                AND PSI.schd_id(+) = PSS.sch_id 
                AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
        /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]         and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]*/) COMPLETED_ITEM_SURVEY,
        --( SELECT COUNT(*) AS OFFERINGS FROM PA_CPNT_SURVEY PCS,PA_STUD_SURVEY PSS WHERE PCS.cpnt_survey_id=pss.cpnt_survey_id and pss.sch_id is not null and PCS.cpnt_survey_id = CS.CPNT_SURVEY_ID) SCHDULE_OFFERING    
        (SELECT COUNT(DISTINCT sch_id) 
         FROM   pa_stud_survey 
         WHERE  sch_id IS NOT NULL 
                AND cpnt_survey_id IN (SELECT PCS.cpnt_survey_id 
                                       FROM   pa_cpnt_survey PCS, 
                                              pa_stud_survey PSS, 
                                              pa_ssg_inst PSI, 
                                              pa_ssg_locn PSL 
                                       WHERE 
                    PCS.cpnt_survey_id = PSS.cpnt_survey_id 
                    AND PSS.sch_id IS NOT NULL 
                    AND PSL.schd_id(+) = PSS.sch_id 
                    AND PSI.schd_id(+) = PSS.sch_id 
                    AND PCS.cpnt_survey_id = CS.cpnt_survey_id 
                                      /** and (PCS.CPNT_TYP_ID,PCS.CPNT_ID,PCS.REV_DTE) IN [ItemSearch] and PSS.STUD_ID IN [UserSearch] and PSS.SCH_ID IN [ScheduledOfferingSearch] and trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                                       and NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate] and PSI.INST_ID IN [InstructorSearch] and PSL.LOCN_ID IN [LocationSearch] and PCS.IS_ACTIVE =  [IncludePreviousItemAssociations]                                       */ 
                                       GROUP  BY ( PCS.cpnt_survey_id )) 
        /** and SCH_ID IN [ScheduledOfferingSearch]*/) 
        SCHDULE_OFFERING 
 FROM   pa_questionnaire_survey QS 
        LEFT OUTER JOIN pa_cpnt_survey CS 
                     ON QS.survey_id = CS.survey_id 
        RIGHT OUTER JOIN pa_stud_survey SS 
                      ON CS.cpnt_survey_id = SS.cpnt_survey_id 
        LEFT OUTER JOIN pa_survey_responses SR 
                     ON SS.stud_survey_id = SR.stud_survey_id 
        LEFT OUTER JOIN pa_survey_question SQ 
                     ON SR.question_id = SQ.question_id 
        LEFT OUTER JOIN pa_survey_section SSEC 
                     ON SQ.section_id = SSEC.section_id 
        LEFT OUTER JOIN pa_question_answer_choices QAC 
                     ON QAC.question_id = SQ.question_id 
        LEFT OUTER JOIN pa_ssg_inst INSTRUCTOR 
                     ON INSTRUCTOR.schd_id = SS.sch_id 
        LEFT OUTER JOIN pa_ssg_locn LOCATION 
                     ON LOCATION.schd_id = SS.sch_id 
        LEFT OUTER JOIN pa_inst INSTDETAIL 
                     ON INSTDETAIL.inst_id = SR.inst_id, 
        --PA_QUESTION_ANSWER_CHOICES QAC, 
        pv_course CPNT--, 
 --PA_SSG_INST INSTRUCTOR, 
 --PA_SSG_LOCN LOCATION  
 --AND ( ( SQ.QUESTION_TYPE = 'MULTIPLE CHOICE' OR SQ.QUESTION_TYPE = 'ONE CHOICE' ) AND ( QAC.ANSWER_ID = SR.STUD_RESPONSE_VALUE OR QAC.ANSWER_ID IS NULL OR SR.STUD_RESPONSE_VALUE IS NULL))
 WHERE  ( QAC.answer_id = SR.stud_response_value 
           OR SR.stud_response_value IS NULL 
           OR SQ.question_type = 'RATING SCALE' ) 
        AND SQ.section_id IS NOT NULL 
        AND SS.survey_status_id = 'COMPLETED' 
        AND CS.cpnt_typ_id = CPNT.cpnt_typ_id 
        AND CS.cpnt_id = CPNT.cpnt_id 
        AND CS.rev_dte = CPNT.rev_dte 
        AND QS.level_indicator = 3 
        --AND ( SS.SCH_ID = INSTRUCTOR.SCHD_ID OR SS.SCH_ID IS NULL ) 
        --AND ( SS.SCH_ID = LOCATION.SCHD_ID OR SS.SCH_ID IS NULL )  
        AND SS.item_completion_date <> (SELECT Min(item_completion_date) 
                                        FROM   pa_stud_survey pss, 
                                               pa_questionnaire_survey PQS, 
                                               pa_cpnt_survey PCS 
                                        WHERE 
            pss.cpnt_survey_id = SS.cpnt_survey_id 
            AND pss.stud_id = SS.stud_id 
            AND PCS.survey_id = PQS.survey_id 
            AND pss.cpnt_survey_id = 
                pcs.cpnt_survey_id 
            AND PQS.level_indicator = 3) 
 /**              and QS.SURVEY_ID in [SurveyIDSearch]        and SS.STUD_ID in [UserSearch]        and trunc(SS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]        and NOT trunc(SS.ITEM_COMPLETION_DATE) > [LearningEventToDate]        and SS.SCH_ID in [ScheduledOfferingSearch]        AND INSTRUCTOR.INST_ID in [InstructorSearch]        AND LOCATION.LOCN_ID in [LocationSearch]        and QS.IS_ACTIVE  = [QuestionnaireSurveyStatus]        and CS.IS_ACTIVE  = [IncludePreviousItemAssociations]        and (CS.cpnt_typ_id,CS.cpnt_id,CS.rev_dte) in [ItemSearch]        and [security:PA_QUESTIONNAIRE_SURVEY QS]   */ 
 GROUP  BY 'Retraining', 
           QS.survey_id, 
           CS.cpnt_survey_id, 
           CPNT.cpnt_title, 
           SQ.section_id, 
           SQ.question_id, 
           Decode(SR.stud_response_value, NULL, SR.stud_response_text, 
                                          SR.stud_response_value), 
           QS.name, 
           CS.cpnt_typ_id, 
           CS.cpnt_id, 
           CS.rev_dte, 
           SSEC.section_order, 
           SSEC.section_title, 
           SSEC.resource_type, 
           SQ.question_order, 
           SQ.question_type, 
           SQ.question_text, 
           SR.stud_response_text, 
           SR.stud_response_value, 
           SQ.rating_scale_id, 
           INSTDETAIL.fname, 
           INSTDETAIL.lname, 
           INSTDETAIL.mi) 
TotalRespondents
SELECT ( CASE 
           WHEN 'Retraining' = ? THEN (SELECT Count(DISTINCT PSR.stud_survey_id) 
                                              AS 
                                              TOTAL_RESPONDENTS 
                                       FROM   pa_survey_responses PSR, 
                                              pa_stud_survey PSS, 
                                              pa_cpnt_survey PCS, 
                                              pa_ssg_inst PSI, 
                                              pa_ssg_locn PSL 
                                       WHERE 
           PSR.stud_survey_id = PSS.stud_survey_id 
           AND PSS.cpnt_survey_id = 
               PCS.cpnt_survey_id 
           AND PSS.sch_id = PSI.schd_id(+) 
           AND PSS.sch_id = PSL.schd_id(+) 
           AND PSS.survey_status_id = 'COMPLETED' 
           AND PCS.survey_id = ? 
           AND PCS.cpnt_survey_id = ? 
           AND PSR.question_id = ? 
           AND PSS.item_completion_date <> 
               (SELECT Min(item_completion_date) 
                FROM   pa_stud_survey SS, 
                       pa_questionnaire_survey QS, 
                       pa_cpnt_survey CS 
                WHERE 
               SS.cpnt_survey_id = 
               PSS.cpnt_survey_id 
               AND SS.stud_id = PSS.stud_id 
               AND CS.survey_id = QS.survey_id 
               AND SS.cpnt_survey_id = 
                   CS.cpnt_survey_id 
               AND QS.level_indicator = 3) 
                                      /**                                             AND trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                                            AND NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]                                            AND PSS.STUD_ID in [UserSearch]                                             AND PSS.SCH_ID in [ScheduledOfferingSearch]                                             AND PSI.INST_ID in [InstructorSearch]                                             AND PSL.LOCN_ID in [LocationSearch]                                        */ 
                                      ) 
           ELSE (SELECT Count(DISTINCT PSR.stud_survey_id) AS TOTAL_RESPONDENTS 
                 FROM   pa_survey_responses PSR, 
                        pa_stud_survey PSS, 
                        pa_cpnt_survey PCS, 
                        pa_ssg_inst PSI, 
                        pa_ssg_locn PSL 
                 WHERE  PSR.stud_survey_id = PSS.stud_survey_id 
                        AND PSS.cpnt_survey_id = PCS.cpnt_survey_id 
                        AND PSS.sch_id = PSI.schd_id(+) 
                        AND PSS.sch_id = PSL.schd_id(+) 
                        AND PSS.survey_status_id = 'COMPLETED' 
                        AND PCS.survey_id = ? 
                        AND PCS.cpnt_survey_id = ? 
                        AND PSR.question_id = ? 
                        AND PSS.item_completion_date = (SELECT 
                            Min(item_completion_date) 
                                                        FROM 
                            pa_stud_survey SS, 
                            pa_questionnaire_survey QS, 
                            pa_cpnt_survey 
                            CS 
                                         WHERE 
                            SS.cpnt_survey_id = PSS.cpnt_survey_id 
                            AND SS.stud_id = PSS.stud_id 
                            AND CS.survey_id = QS.survey_id 
                            AND SS.cpnt_survey_id = CS.cpnt_survey_id 
                            AND QS.level_indicator = 3) 
                /**                       AND trunc(PSS.ITEM_COMPLETION_DATE) >= [LearningEventFromDate]                       AND NOT trunc(PSS.ITEM_COMPLETION_DATE) > [LearningEventToDate]                       AND PSS.STUD_ID in [UserSearch]                       AND PSS.SCH_ID in [ScheduledOfferingSearch]                       AND PSI.INST_ID in [InstructorSearch]                       AND PSL.LOCN_ID in [LocationSearch]                  */ 
                ) 
         END ) AS TOTAL_RESPONDENTS 
FROM   dual 

 

Link: http://wiki.scn.sap.com/wiki/x/oIERG

 

 

  • No labels