ITS_OP/SRC/SQL/현재소통정보조회.sql

SELECT A.LINK_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, MISS_YN,   
        PRCN_SPED, SMTH_SPED, ADJS_SPED, PTRN_SPED, PRCN_SORC, ADJS_SORC, DATA_NUM, 
        (CASE WHEN PRCN_DT <= TO_CHAR(SYSDATE - 10/1440, 'YYYYMMDDHH24MISS')        
              THEN 'Y' ELSE 'N' END) AS MISSING,                                    
        (CASE WHEN PRCN_DT >= TO_CHAR(SYSDATE - 15/1440, 'YYYYMMDDHH24MISS')        
              THEN 'N' ELSE 'Y' END) AS LASTANS,                                    
        (SELECT MAX(PRCN_DT) FROM TB_LINK_TRAF) MPRCN_DT                            
   FROM TB_LINK_TRAF A, TB_LINK B                                                   
  WHERE A.LINK_ID = B.LINK_ID                                                       



SELECT A.IFSC_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, PTRN_SPED, DATA_NUM, 
        (CASE WHEN PRCN_DT <= TO_CHAR(SYSDATE - 10/1440, 'YYYYMMDDHH24MISS')        
              THEN 'Y' ELSE 'N' END) AS MISSING,                                    
        (CASE WHEN PRCN_DT >= TO_CHAR(SYSDATE - 15/1440, 'YYYYMMDDHH24MISS')        
              THEN 'N' ELSE 'Y' END) AS LASTANS,                                    
        (SELECT MAX(PRCN_DT) FROM TB_LINK_TRAF) MPRCN_DT                            
  FROM TB_IFSC_TRAF A, TB_IFSC B
 WHERE A.IFSC_ID = B.IFSC_ID
 


SELECT A.ROAD_ID, PRCN_DT, TFVL, SPED, PTRN_SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, DATA_NUM, 
       (CASE WHEN PRCN_DT <= TO_CHAR(SYSDATE - 10/1440, 'YYYYMMDDHH24MISS')        
             THEN 'Y' ELSE 'N' END) AS MISSING,                                    
       (CASE WHEN PRCN_DT >= TO_CHAR(SYSDATE - 15/1440, 'YYYYMMDDHH24MISS')        
             THEN 'N' ELSE 'Y' END) AS LASTANS,                                    
       (SELECT MAX(PRCN_DT) FROM TB_ROAD_TRAF) MPRCN_DT                            
  FROM TB_ROAD_TRAF A, TB_ROAD B 
 WHERE A.ROAD_ID = B.ROAD_ID



SELECT A.LINK_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, MISS_YN,     
       PRCN_SPED, SMTH_SPED, ADJS_SPED, PTRN_SPED, PRCN_SORC, ADJS_SORC, DATA_NUM,
       (CASE WHEN PRCN_DT <= TO_CHAR(SYSDATE - 10/1440, 'YYYYMMDDHH24MISS') THEN 'Y' ELSE 'N' END) AS MISSING,
       (SELECT MAX(PRCN_DT) FROM TB_LINK_TRAF) MPRCN_DT
  FROM TB_LINK_TRAF A, TB_LINK B                                                                 
 WHERE A.LINK_ID = B.LINK_ID


SELECT LINK_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, MISS_YN, 
       PRCN_SPED, SMTH_SPED, ADJS_SPED, PTRN_SPED, PRCN_SORC, ADJS_SORC, DATA_NUM, 
       (SELECT MAX(PRCN_DT) FROM TB_LINK_TRAF) MPRCN_DT
  FROM TB_LINK_TRAF
 WHERE TO_DATE(PRCN_DT, 'YYYYMMDDHH24MISS') > sysdate - 10 / 1440
UNION 
SELECT LINK_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, '' CMTR_GRAD_CD, MISS_YN, 
       PRCN_SPED, SMTH_SPED, ADJS_SPED, PTRN_SPED, PRCN_SORC, ADJS_SORC, DATA_NUM, 
       (SELECT MAX(PRCN_DT) FROM TB_LINK_TRAF) MPRCN_DT
  FROM TB_LINK_TRAF
 WHERE TO_DATE(PRCN_DT, 'YYYYMMDDHH24MISS') <= sysdate - 10 / 1440
 ORDER BY LINK_ID, PRCN_DT



SELECT IFSC_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, PTRN_SPED, DATA_NUM 
  FROM TB_IFSC_TRAF
 WHERE TO_DATE(PRCN_DT, 'YYYYMMDDHH24MISS') > sysdate - 10 / 1440 
UNION 
SELECT IFSC_ID, PRCN_DT, TFVL, SPED, OCPY_RATE, TRVL_HH, '' CMTR_GRAD_CD, PTRN_SPED, DATA_NUM 
  FROM TB_IFSC_TRAF 
 WHERE TO_DATE(PRCN_DT, 'YYYYMMDDHH24MISS') <= sysdate - 10 / 1440 
 ORDER BY IFSC_ID, PRCN_DT