RB_AG_EF_RESP_V

(SQL View)
 Index Back

Emails closed after due date

Used for ERMS reports....................

SELECT A.OPRID , 0 , 0 , 0 , 0 , 0 , 0 , 0 , B.SLA_EXT_BASE_DTTM , A.ROW_ADDED_DTTM , COUNT(*) OVER (PARTITION BY OPRID ,TO_DATE(TO_CHAR(A.ROW_ADDED_DTTM ,'YYYY-MM-DD') ,'YYYY-MM-DD')) , COUNT(*) OVER(PARTITION BY A.OPRID ,SUBSTR(A.ROW_ADDED_DTTM ,1 ,10) || ' [' || CASE WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '01' THEN '01am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '02' THEN '02am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '03' THEN '03am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '04' THEN '04am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '05' THEN '05am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '06' THEN '06am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '07' THEN '07am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '08' THEN '08am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '09' THEN '09am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '10' THEN '10am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '11' THEN '11am' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '12' THEN '12pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '13' THEN '01pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '14' THEN '02pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '15' THEN '03pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '16' THEN '04pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '17' THEN '05pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '18' THEN '06pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '19' THEN '07pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '20' THEN '08pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '21' THEN '09pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '22' THEN '10pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '23' THEN '11pm' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '00' THEN '12am' END || '-' || CASE WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '12' THEN '01 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '11' THEN '12 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '10' THEN '11 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '09' THEN '10 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '08' THEN '09 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '07' THEN '08 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '06' THEN '07 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '05' THEN '06 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '04' THEN '05 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '03' THEN '04 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '02' THEN '03 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '01' THEN '02 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '00' THEN '01 am]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '13' THEN '02 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '14' THEN '03 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '15' THEN '04 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '16' THEN '05 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '17' THEN '06 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '18' THEN '07 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '19' THEN '08 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '20' THEN '09 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '21' THEN '10 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '22' THEN '11 pm]' WHEN SUBSTR(A.ROW_ADDED_DTTM ,12 ,2) = '23' THEN '12 am]' END) , COUNT(*) OVER (PARTITION BY A.OPRID ,TO_CHAR(CAST((A.ROW_ADDED_DTTM) AS TIMESTAMP) ,'Month,YYYY')) , COUNT(*) OVER (PARTITION BY A.OPRID ,((TO_DATE(TO_DATE(TO_CHAR(A.ROW_ADDED_DTTM ,'YYYY-MM-DD') ,'YYYY-MM-DD') ,'YYYY-MM-DD')) + ((MOD((((TO_DATE(TO_DATE(TO_CHAR(A.ROW_ADDED_DTTM ,'YYYY-MM-DD') ,'YYYY-MM-DD') ,'YYYY-MM-DD')) - (TO_DATE(1900 || '-' || 01 || '-' || 01 , 'YYYY-MM-DD')))) ,7)*-1)))) FROM PS_RB_AGNT_RESP_VW A , PS_RB_IN_EMAIL B WHERE B.INTERACTION_ID = A.ROOT_INTER_ID GROUP BY A.OPRID,SLA_EXT_BASE_DTTM,A.ROW_ADDED_DTTM

# 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 RB_CLOSED_DUEDATE Number(8,0) INTEGER NOT NULL Detail item status
3 RB_EMAIL_ASSIGNED Number(8,0) INTEGER NOT NULL Detail item status
4 RB_REP_WITH_TEMPL Number(8,0) INTEGER NOT NULL Used for ERMS Reporting.......................
5 RB_CLOSED_CAN Number(8,0) INTEGER NOT NULL Detail item status
6 RB_CLOSED_DUP Number(8,0) INTEGER NOT NULL Detail item status
7 RB_CLOSED_REP Number(8,0) INTEGER NOT NULL Detail item status
8 RB_CLOSED_SPAM Number(8,0) INTEGER NOT NULL Detail item status
9 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
10 CLOSED_DTTM DateTime(26) TIMESTAMP Date Time stamp for when the case status category was changed to closed.
11 ACTIVE Number(5,0) INTEGER NOT NULL Active
12 ABE_SEQ Number(6,0) INTEGER NOT NULL Address Book sequence number.
13 ABE_SEQ_MAX Number(6,0) INTEGER NOT NULL This field is used during the Address Book entry upgrade and Data Import to hold the highest ABE Sequence.
14 COUNT_TOTAL Number(9,0) DECIMAL(9) NOT NULL Total Count