ITS-OP/SRC/SQL/프로세스 이력조회.sql

SELECT X.CRTN_IDX, X.HHMM, Y.SYST_ID, Y.SYST_NM, Y.CRTN_DT, Y.SYST_STTS_CD, Y.RMRK
  FROM (SELECT ROWNUM AS CRTN_IDX, 
               TO_CHAR(TRUNC(SYSDATE)+((LEVEL-1)*5)/1440, 'HH24:MI') AS HHMM 
          FROM DUAL 
          CONNECT BY LEVEL <= 288 )X,
       (SELECT A.SYST_ID, A.SYST_NM, B.CRTN_DT, TRUNC((B.CHOUR * 60 + B.CMIN) / 5, 0)+1 AS CRTN_IDX, B.SYST_STTS_CD, B.RMRK   
          FROM TB_UNIT_SYST A,                                           
               (SELECT SYST_ID, CRTN_DT,
                       TO_NUMBER(SUBSTR(CRTN_DT, 9, 2)) AS CHOUR, TO_NUMBER(SUBSTR(CRTN_DT, 11, 2)) AS CMIN, 
                       SYST_STTS_CD, RMRK            
                   FROM TB_UNIT_SYST_STTS_HS                              
                   WHERE SYST_ID = 'CTV01'                               
                     AND CRTN_DT BETWEEN '20140101000000' AND '20141231235959'                
               ) B                                                     
         WHERE A.SYST_ID = 'CTV01'                                        
           AND A.SYST_ID = B.SYST_ID) Y                                  
 WHERE X.CRTN_IDX = Y.CRTN_IDX(+)
 ORDER BY X.CRTN_IDX