ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/교통정보 가공결과 분석.sql

SELECT A.PRCN_DT, TO_DATE(A.PRCN_DT, 'YYYYMMDDHH24MISS') AS PRCN_DT2,
       (UTIS26_CNT + UTIS01_CNT + UTIS02_CNT + OPER01_CNT ) CRTN_CNT, 
       UTIS26_CNT, UTIS01_CNT, UTIS02_CNT, OPER01_CNT, B.ADJS_CNT 
  FROM (SELECT PRCN_DT, 
               MAX(DECODE(CLCT_SYST_CD, 'UTIS26', FUSN_NUM, NULL)) UTIS26_CNT, 
               MAX(DECODE(CLCT_SYST_CD, 'UTIS01', FUSN_NUM, NULL)) UTIS01_CNT, 
               MAX(DECODE(CLCT_SYST_CD, 'UTIS02', FUSN_NUM, NULL)) UTIS02_CNT, 
               MAX(DECODE(CLCT_SYST_CD, 'OPER01', FUSN_NUM, NULL)) OPER01_CNT 
          FROM TB_CLCT_SYST_STTS_HS 
         WHERE PRCN_DT BETWEEN '20140619000000' 
                           AND '20140619235959' 
         GROUP BY PRCN_DT 
       ) A, 
       (SELECT PRCN_DT, ADJS_NUM AS ADJS_CNT 
          FROM TB_TRFC_PRCN_STAT 
         WHERE PRCN_DT BETWEEN '20140619000000' 
                           AND '20140619235959' 
       ) B 
 WHERE A.PRCN_DT = B.PRCN_DT 
 ORDER BY PRCN_DT