EP_SUB_ROLE_VW(SQL View) |
Index Back |
|---|---|
Average SubItem rating by roleUsed to calc the average rating for a subitem by role |
| SELECT A.EP_APPRAISAL_ID , A.EP_SECTION_TYPE , A.EP_ITEM_ID , A.EP_ITEM_SEQ , A.EP_SUB_ID , A.EP_SUB_SEQ , B.EP_ROLE , COUNT(*) , AVG(A.EP_RATING) FROM PS_EP_APPR_SUBITEM A , PS_EP_APPR_ROLE B , PS_EP_APPR_SECTION D WHERE A.EP_APPRAISAL_ID = B.EP_APPRAISAL_ID AND A.EP_ROLE = B.EP_ROLE AND A.EP_REVIEWER_ID = B.EP_REVIEWER_ID AND A.EP_APPRAISAL_ID = D.EP_APPRAISAL_ID AND A.EP_ROLE = D.EP_ROLE AND A.EP_REVIEWER_ID = D.EP_REVIEWER_ID AND A.EP_SECTION_TYPE = D.EP_SECTION_TYPE AND D.EP_SUB_ROLE_RT_SW = 'Y' AND A.REVIEW_RATING <> ' ' AND B.EP_REVIEW_STATUS <> 'CA' AND B.EP_REVIEW_STATUS <> 'IP' GROUP BY A.EP_APPRAISAL_ID, A.EP_SECTION_TYPE, A.EP_ITEM_ID, A.EP_ITEM_SEQ, A.EP_SUB_ID, A.EP_SUB_SEQ, B.EP_ROLE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EP_APPRAISAL_ID | Number(8,0) | INTEGER NOT NULL | Document ID |
| 2 | EP_SECTION_TYPE | Character(8) | VARCHAR2(8) NOT NULL | Section type |
| 3 | EP_ITEM_ID | Character(12) | VARCHAR2(12) NOT NULL | Item unique identifier |
| 4 | EP_ITEM_SEQ | Number(2,0) | SMALLINT NOT NULL | Item sequence number |
| 5 | EP_SUB_ID | Character(12) | VARCHAR2(12) NOT NULL | Sub-Item unique identifier |
| 6 | EP_SUB_SEQ | Number(2,0) | SMALLINT NOT NULL | Sub-Item sequence number |
| 7 | EP_ROLE | Character(8) | VARCHAR2(8) NOT NULL | Role unique identifier |
| 8 | EP_COUNT | Number(2,0) | SMALLINT NOT NULL | Used as a dummy counter. |
| 9 | EP_RATING | Number(6,2) | DECIMAL(5,2) NOT NULL | Rating |