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

--레벨1
SELECT A.LINK_ID AS LINKID, 
       DECODE(CMTR_GRAD_CD, 'LTC0', 0, 'LTC1', 25, 'LTC2', 15, 'LTC3', 5, -1) AS SPEED,
       NVL(PRCN_DT, '20000411000000' ) AS REGDATE
  FROM (SELECT LINK_ID, PRCN_DT, SPED, CMTR_GRAD_CD 
          FROM TB_LINK_TRAF 
         WHERE PRCN_DT > TO_CHAR(SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS') 
        ) A 
 ORDER BY LINKID

--서비스링크(레벨2)
--SELECT IFSC_ID AS IFSCID,
--       DECODE(CMTR_GRAD_CD, 'LTC0', 0, 'LTC1', 25, 'LTC2', 15, 'LTC3', 5, 0) AS SPEED,
--       NVL(PRCN_DT, '20131111111111' ) AS REGDATE
--  FROM (SELECT IFSC_ID, PRCN_DT, SPED, CMTR_GRAD_CD
--          FROM TB_IFSC_TRAF
--         WHERE PRCN_DT > TO_CHAR(SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS')
--       ) B
-- ORDER BY IFSCID

--도로(레벨3)
--SELECT ROAD_ID AS ROADID,
--       DECODE( CMTR_GRAD_CD, 'LTC0', 0, 'LTC1', 25, 'LTC2', 15, 'LTC3', 5, 0 ) AS SPEED,
--       NVL( PRCN_DT, '20131111111111' ) AS REGDATE
--  FROM (SELECT ROAD_ID, PRCN_DT, SPED, CMTR_GRAD_CD
--          FROM TB_ROAD_TRAF
--         WHERE PRCN_DT > TO_CHAR(SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS')
--		   ) B
-- ORDER BY ROADID
 
SELECT A.LINK_ID          AS LINKID, 
       NVL(B.SPED,     0) AS SPEED, 
       B.PRCN_DT          AS REGDATE,
       NVL(B.DATA_NUM, 0) AS DATACNT,
       NVL(B.TRVL_HH,  0) AS TRVTM
  FROM TB_LINK A,
       (SELECT * 
          FROM TB_LINK_TRAF 
         WHERE PRCN_DT > TO_CHAR (SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS') 
       ) B
 WHERE A.LINK_ID = B.LINK_ID(+)
 ORDER BY LINKID
 
SELECT A.IFSC_ID          AS LINKID, 
       NVL(B.SPED,     0) AS SPEED, 
       B.PRCN_DT          AS REGDATE,
       NVL(B.DATA_NUM, 0) AS DATACNT,
       NVL(B.TRVL_HH,  0) AS TRVTM
  FROM TB_IFSC A,
       (SELECT *
          FROM TB_IFSC_TRAF
         WHERE PRCN_DT > TO_CHAR(SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS')
       ) B
 WHERE A.IFSC_ID = B.IFSC_ID(+)
 ORDER BY LINKID

 
SELECT A.ROAD_ID          AS LINKID,
       NVL(B.SPED,     0) AS SPEED, 
       B.PRCN_DT          AS REGDATE,
       NVL(B.DATA_NUM, 0) AS DATACNT,
       NVL(B.TRVL_HH,  0) AS TRVTM
  FROM TB_ROAD A,
       (SELECT *
          FROM TB_ROAD_TRAF
         WHERE PRCN_DT > TO_CHAR(SYSDATE - 10 / 1440, 'YYYYMMDDHH24MISS')
		   ) B
 WHERE A.ROAD_ID = B.ROAD_ID(+)
 ORDER BY LINKID
 
 select * from TB_ROAD_TRAF