ITS-OP/SRC/SQL/교통정보 제공결과 분석.sql

  case enLinkType_Link :
   SELECT PRCN_DT, 
          (SELECT COUNT(1) CNT FROM TB_LINK WHERE DEL_YN = 'N') Total, 
          (LINK_LIGT_NUM + LINK_DELY_NUM + LINK_CNGS_NUM) GenerateCount, 
          LINK_LIGT_NUM G1, 
          LINK_DELY_NUM G2, 
          LINK_CNGS_NUM G3, 
          (SELECT COUNT(1) CNT FROM TB_LINK  WHERE DEL_YN = 'N') - LINK_LIGT_NUM - LINK_DELY_NUM - LINK_CNGS_NUM NoneDataCount 
     FROM TB_TRFC_PRCN_STAT 
    WHERE PRCN_DT BETWEEN '20140619000000' AND '20140619235959' 
    ORDER BY PRCN_DT

  case enLinkType_ServiceLink :
   SELECT PRCN_DT PRCN_DT, 
          (SELECT COUNT(1) CNT FROM TB_IFSC  WHERE DEL_YN = 'N') Total, 
          (IFSC_LIGT_NUM + IFSC_DELY_NUM + IFSC_CNGS_NUM) GenerateCount, 
          IFSC_LIGT_NUM G1, 
          IFSC_DELY_NUM G2, 
          IFSC_CNGS_NUM G3, 
          (SELECT COUNT(1) CNT FROM TB_IFSC WHERE DEL_YN = 'N') - IFSC_LIGT_NUM - IFSC_DELY_NUM - IFSC_CNGS_NUM NoneDataCount 
     FROM TB_TRFC_PRCN_STAT 
    WHERE PRCN_DT BETWEEN '20140619000000' AND '20140619235959' 
    ORDER BY PRCN_DT

  case enLinkType_Road :
   SELECT PRCN_DT, 
          (SELECT COUNT(1) CNT FROM TB_ROAD WHERE DEL_YN = 'N') Total, 
          (ROAD_LIGT_NUM + ROAD_DELY_NUM + ROAD_CNGS_NUM) GenerateCount, 
          ROAD_LIGT_NUM G1, 
          ROAD_DELY_NUM G2, 
          ROAD_CNGS_NUM G3, 
          (SELECT COUNT(1) CNT FROM TB_ROAD WHERE DEL_YN = 'N') - ROAD_LIGT_NUM - ROAD_DELY_NUM - ROAD_CNGS_NUM NoneDataCount 
     FROM TB_TRFC_PRCN_STAT 
    WHERE PRCN_DT BETWEEN '20140619000000' AND '20140619235959' 
    ORDER BY PRCN_DT

  case enLinkType_VMSServiceLink :
   SELECT PRCN_DT, 
          (SELECT COUNT(1) CNT FROM TB_VMS_IFSC) Total, 
          (VMS_IFSC_LIGT_NUM + VMS_IFSC_DELY_NUM + VMS_IFSC_CNGS_NUM) GenerateCount, 
          VMS_IFSC_LIGT_NUM G1, 
          VMS_IFSC_DELY_NUM G2, 
          VMS_IFSC_CNGS_NUM G3, 
          (SELECT COUNT(1) CNT FROM TB_VMS_IFSC) - VMS_IFSC_LIGT_NUM - VMS_IFSC_DELY_NUM - VMS_IFSC_CNGS_NUM NoneDataCount 
     FROM TB_TRFC_PRCN_STAT 
    WHERE PRCN_DT BETWEEN '20140619000000' AND '20140619235959' 
    ORDER BY PRCN_DT