Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
SELECT N_RFSTRACK,
DECODE(N_STATUS,1,D_STATUSDATE,NULL) STATUS_1_DATE,
DECODE(N_STATUS,1,C_STATUSTIME,NULL) STATUS_1_TIME,
DECODE(N_STATUS,2,D_STATUSDATE,NULL) STATUS_2_DATE,
DECODE(N_STATUS,2,C_STATUSTIME,NULL) STATUS_2_TIME,
.
.
.
DECODE(N_STATUS,20,D_STATUSDATE,NULL) STATUS_20_DATE,
DECODE(N_STATUS,20,C_STATUSTIME,NULL) STATUS_20_TIME
FROM
(
SELECT N_RFSTRACK, MIN(D_UPDATEDATE) D_STATUSDATE, MIN(C_UPDATETIME) C_STATUSTIME, N_STATUS
FROM
RFSUPDATES,
RFSSTATUS
WHERE
RFSSTATUS.N_ID = RFSUPDATES.N_STATUS
GROUP BY N_RFSTRACK, N_STATUS
)
SELECT N_RFSTRACK,
DECODE(SUM(DECODE(N_STATUS,1,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,1,STATUSDATE,0)),'YYYYMMDD')) STATUS_1_DATE,
DECODE(SUM(DECODE(N_STATUS,1,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,1,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,1,STATUSTIME,0))),3,2)) STATUS_1_TIME,
DECODE(SUM(DECODE(N_STATUS,2,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,2,STATUSDATE,0)),'YYYYMMDD')) STATUS_2_DATE,
DECODE(SUM(DECODE(N_STATUS,2,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,2,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,2,STATUSTIME,0))),3,2)) STATUS_2_TIME,
.
.
.
DECODE(SUM(DECODE(N_STATUS,20,STATUSDATE,0)),0,NULL,TO_DATE(SUM(DECODE(N_STATUS,20,STATUSDATE,0)),'YYYYMMDD')) STATUS_20_DATE,
DECODE(SUM(DECODE(N_STATUS,20,STATUSTIME,0)),0,NULL,SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,20,STATUSTIME,0))),1,2) || ':' || SUBSTR(TO_CHAR(SUM(DECODE(N_STATUS,20,STATUSTIME,0))),3,2)) STATUS_20_TIME
FROM
(
SELECT
N_RFSTRACK,
TO_NUMBER(TO_CHAR(D_STATUSDATE,'YYYYMMDD')) STATUSDATE,
TO_NUMBER(SUBSTR(C_STATUSTIME,1,2)||SUBSTR(C_STATUSTIME,4,2)) STATUSTIME,
N_STATUS
FROM
(
SELECT
N_RFSTRACK,
MIN(D_UPDATEDATE) D_STATUSDATE,
MIN(C_UPDATETIME) C_STATUSTIME,
N_STATUS
FROM
RFSUPDATES,
RFSSTATUS
WHERE
RFSSTATUS.N_ID = RFSUPDATES.N_STATUS
GROUP BY N_RFSTRACK, N_STATUS
)
)
GROUP BY N_RFSTRACK