ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/시설물 통신단절 통계.sql

--시설물 통신단절 통계(시설물별 상태정보 입력여부 확인)
case enFacilityType_CCTV :
SELECT A.WEB_CMRA_NMBR FCLT_ID, A.ISTL_LCTN_NM FCLT_NM, NVL(B.CNT, 0) CNT_CMS1, NVL(C.CNT, 0) CNT_CMS2 
  FROM TB_WEB_CMRA A, 
       (SELECT WEB_CMRA_NMBR, COUNT(WEB_CMRA_NMBR) CNT FROM TB_WEB_CMRA_STTS_HS WHERE CMNC_STTS_CD = 'CMS1' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY WEB_CMRA_NMBR) B, 
       (SELECT WEB_CMRA_NMBR, COUNT(WEB_CMRA_NMBR) CNT FROM TB_WEB_CMRA_STTS_HS WHERE CMNC_STTS_CD = 'CMS2' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY WEB_CMRA_NMBR) C 
 WHERE A.WEB_CMRA_NMBR = B.WEB_CMRA_NMBR(+) 
   AND A.WEB_CMRA_NMBR = C.WEB_CMRA_NMBR(+) 
   AND A.DEL_YN = 'N' 
 ORDER BY TO_NUMBER(A.WEB_CMRA_NMBR)

case enFacilityType_VMS :
SELECT A.VMS_CTLR_NMBR FCLT_ID, A.VMS_NM FCLT_NM, NVL(B.CNT, 0) CNT_CMS1, NVL(C.CNT, 0) CNT_CMS2 
  FROM TB_VMS_CTLR A, 
       (SELECT VMS_CTLR_NMBR, COUNT(VMS_CTLR_NMBR) CNT FROM TB_VMS_STTS_HS WHERE CONN_STTS_CD = 'CMS1' AND RGST_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY VMS_CTLR_NMBR) B, 
       (SELECT VMS_CTLR_NMBR, COUNT(VMS_CTLR_NMBR) CNT FROM TB_VMS_STTS_HS WHERE CONN_STTS_CD = 'CMS2' AND RGST_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY VMS_CTLR_NMBR) C 
 WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR(+) 
   AND A.VMS_CTLR_NMBR = C.VMS_CTLR_NMBR(+) 
   AND A.DEL_YN = 'N' 
 ORDER BY TO_NUMBER(A.VMS_CTLR_NMBR)

case enFacilityType_DSRC :
SELECT A.ID FCLT_ID, A.ISTL_LCTN_NM FCLT_NM, NVL(B.CNT, 0) CNT_CMS1, NVL(C.CNT, 0) CNT_CMS2 
  FROM TB_RSE_MSTR A, 
       (SELECT ID, COUNT(ID) CNT FROM TB_RSE_STTS_HS WHERE CMNC_STTS = '1' AND CLCT_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY ID) B, 
       (SELECT ID, COUNT(ID) CNT FROM TB_RSE_STTS_HS WHERE CMNC_STTS = '2' AND CLCT_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY ID) C 
 WHERE A.ID = B.ID(+) 
   AND A.ID = C.ID(+) 
   AND A.DEL_YN = 'N' 
 ORDER BY TO_NUMBER(A.ID)