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 (CSV)
REPORT_NAME_IDFollowUpEvaluationCSV
REPORT_ID382
CATEGORY_IDLearning
GROUP_IDFollowUpEvaluation
WORKFLOW_IDRun FollowUpEvaluation Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENUYes
DMN_IDPUBLIC

 

 

EvaluationDataSet
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, 
       SR.stud_response_value, 
       Decode(SQ.question_type, 'RATING SCALE', (SELECT 
       pa_rating_scale_label.rating_label 
                                                 FROM   pa_rating_scale_label 
                                                 WHERE 
       pa_rating_scale_label.rating = SR.stud_response_value 
       AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id), 
                                'OPEN ENDED', Max(SR.stud_response_text), 
                                Max(QAC.choice_label))             ANSWER, 
       COUNT(DISTINCT SR.response_id)                              ANSWER_COUNT, 
       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(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(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(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(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(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, 
       ( 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 = QS.survey_id 
                                              AND section_id = SQ.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 = QS.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 = CS.cpnt_survey_id 
AND PSQ.section_id = SQ.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 PSS.ITEM_COMPLETION_DATE > [LearningEventFromDate]  and NOT 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, 
CONCAT(( INSTDETAIL.fname ), CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                          CONCAT(' ', ( INSTDETAIL.mi ))))) 
                                             INSTRUCTOR_NAME 
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_inst INSTDETAIL 
                    ON INSTDETAIL.inst_id = SR.inst_id 
       LEFT OUTER JOIN pa_ssg_locn LOCATION 
                    ON LOCATION.schd_id = SS.sch_id, 
       pv_course CPNT 
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.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, 
        SR.stud_response_value, 
        Decode(SQ.question_type, 'RATING SCALE', (SELECT 
        pa_rating_scale_label.rating_label 
                                                  FROM   pa_rating_scale_label 
                                                  WHERE 
        pa_rating_scale_label.rating = SR.stud_response_value 
        AND pa_rating_scale_label.rating_scale_id = SQ.rating_scale_id), 
                                 'OPEN ENDED', Max(SR.stud_response_text), 
                                 Max(QAC.choice_label))             ANSWER, 
        COUNT(DISTINCT SR.response_id)                              ANSWER_COUNT 
        , 
        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(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(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(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(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(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, 
        ( 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 = QS.survey_id 
                                               AND section_id = SQ.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 = QS.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 = CS.cpnt_survey_id 
AND PSQ.section_id = SQ.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 PSS.ITEM_COMPLETION_DATE > [LearningEventFromDate]  and NOT 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 )                                                     MEAN_SCORE, 
CONCAT(( INSTDETAIL.fname ), CONCAT(' ', CONCAT(( INSTDETAIL.lname ), 
                          CONCAT(' ', ( INSTDETAIL.mi ))))) 
                                             INSTRUCTOR_NAME 
 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_inst INSTDETAIL 
                     ON INSTDETAIL.inst_id = SR.inst_id 
        LEFT OUTER JOIN pa_ssg_locn LOCATION 
                     ON LOCATION.schd_id = SS.sch_id, 
        pv_course CPNT--, 
 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.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) 

 

 

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

 

 

  • No labels