RY_EE_SRCH

(SQL View)
 Index Back

Employee Engagement Surveys

View that shows the employee engagement surveys with minimum, maximum, average scores.

SELECT C.OPRID , A.BUSINESS_UNIT , A.DIALOG_ID , A.RY_DIALOG_STATUS , A.RYE_OWNER , A.START_DT , A.END_DT , A.RY_DIALOG_NAME , COUNT(*) AS RY_RESPONDED_COUNT , MIN(B.RY_SRVY_SCORE) AS RY_SRVY_MIN_SCORE , MAX(B.RY_SRVY_SCORE) AS RY_SRVY_MAX_SCORE , %Round(AVG(B.RY_SRVY_SCORE) , 0) AS RY_SRVY_AVG_SCORE , ( SELECT SR.RY_SVY_RATING FROM PS_RY_SVY_SCORES SR WHERE SR.DIALOG_ID = A.DIALOG_ID AND SR.RY_SVY_MAX_SCORE >= ( SELECT %Round(AVG(EE4.RY_SRVY_SCORE) , 0) FROM PS_RY_EE_FACT EE4 WHERE SR.DIALOG_ID = EE4.DIALOG_ID) AND SR.RY_SVY_MIN_SCORE <= ( SELECT %Round(AVG(EE4.RY_SRVY_SCORE) , 0) FROM PS_RY_EE_FACT EE4 WHERE EE4.DIALOG_ID = SR.DIALOG_ID)) AS RY_SVY_RATING FROM PS_RYEDIALOG A, PS_RY_EE_FACT B, PS_RY_SVY_SCRTY_VW C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.DIALOG_ID = B.DIALOG_ID AND A.DIALOG_ID = C.DIALOG_ID AND A.RY_DIALOG_STATUS NOT IN ('D', 'Q') GROUP BY C.OPRID, A.BUSINESS_UNIT, A.DIALOG_ID, A.RYE_OWNER, A.RY_DIALOG_STATUS, A.START_DT, A.END_DT, A.RY_DIALOG_NAME

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 DIALOG_ID Number(18,0) DECIMAL(18) NOT NULL Dialog ID field to identify both design time and run time dialog.
4 RY_STATUS_TRN Character(1) VARCHAR2(1) NOT NULL Dialog Status field used to do translates.
A=Live
C=Archived
D=In Design
I=Completed
P=Paused
Q=Testing
S=Broadcast Hold
5 RYE_OWNER Character(30) VARCHAR2(30) NOT NULL Dialog Designer:Owner ID used for Dialogs
6 START_DT Date(10) DATE Start date field to store start date value.
7 END_DT Date(10) DATE This field stores the end date values.
8 RY_DIALOG_NAME Character(254) VARCHAR2(254) NOT NULL Field is used to capture a Dialog Name.
9 RY_RESPONDED_COUNT Number(10,0) DECIMAL(10) NOT NULL Employee engagement survey responded counts.
10 RY_SRVY_MIN_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Survey Overall score -- overall running score for a survey instance.
11 RY_SRVY_MAX_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Survey Overall score -- overall running score for a survey instance.
12 RY_SRVY_AVG_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Workforce Connect Survey Overall score -- overall running score for a survey instance.
13 RY_SVY_RATING Character(2) VARCHAR2(2) NOT NULL Survey Rating code field