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

SELECT A.LINK_ID ID,  
       (NVL(TRIM(C.NODE_NAME), '값없음')||' - '||NVL(TRIM(D.NODE_NAME), '값없음')) SECT_NAME,  
       B.LINK_LENG SECT_LNGT,  
       A.DATES, A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH  
  FROM (SELECT LINK_ID,  
               SUBSTR(STAT_DT, 1, 4) DATES, --dateformat
               TFVL, SPED, OCPY_RATE, TRVL_HH  
          FROM TB_LINK_YY_STAT A  
         WHERE LINK_ID = '3850428200'  
           AND STAT_DT BETWEEN '20140101000000' AND '20141231235959' 
       ) A,  
       TB_LINK B, TB_NODE C, TB_NODE D  
 WHERE B.DEL_YN = 'N'  
   AND A.LINK_ID = B.LINK_ID  
   AND B.F_NODE = C.NODE_ID(+)  
   AND B.T_NODE = D.NODE_ID(+)  
 ORDER BY DATES


SELECT A.IFSC_ID ID,
       (NVL(TRIM(C.NODE_NAME), '값없음')||' - '||NVL(TRIM(D.NODE_NAME), '값없음')) SECT_NAME,
       B.SECT_LNGT SECT_LNGT,
       A.DATES, A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
  FROM (SELECT IFSC_ID,
               SUBSTR(STAT_DT, 1, 6) DATES, 
               TFVL, SPED, OCPY_RATE, TRVL_HH
          FROM TB_IFSC_YY_STAT A 
         WHERE IFSC_ID = '3850428200'
           AND STAT_DT BETWEEN '20140101000000' AND '20141231235959' 
       ) A, 
       TB_IFSC B, TB_NODE C, TB_NODE D 
  WHERE B.DEL_YN = 'N' 
    AND A.IFSC_ID = B.IFSC_ID 
    AND B.F_NODE_ID = C.NODE_ID(+) 
    AND B.T_NODE_ID = D.NODE_ID(+) 
 ORDER BY DATES