- Created by Deepak Kumar Shah on Feb 04, 2015
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 Name | Follow-Up Evaluation (CSV) |
|---|---|
| REPORT_NAME_ID | FollowUpEvaluationCSV |
| REPORT_ID | 382 |
| CATEGORY_ID | Learning |
| GROUP_ID | FollowUpEvaluation |
| WORKFLOW_ID | Run FollowUpEvaluation Report |
| TYPE | BIRT /PRD |
| USER_TYPE | Admin |
| SHOW_IN_MENU | Yes |
| DMN_ID | PUBLIC |
EvaluationDataSet
Expand source
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