123456789101112131415161718192021222324252627282930 |
- --시설물 통신단절 통계(시설물별 상태정보 입력여부 확인)
- 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)
|