ITS_OP/SRC/SQL/보고서-시설물.sql

SELECT FACTYPE, NVL(CMS0CNT, 0) CMS0CNT, NVL(CMS1CNT, 0) CMS1CNT
  FROM (
SELECT 'VMS' AS FACTYPE, SUM(CMS0CNT)AS CMS0CNT, SUM(CMS1CNT) AS CMS1CNT
  FROM (SELECT DECODE(CONN_STTS_CD, 'CMS0', 1, 0) AS CMS0CNT, 
               DECODE(CONN_STTS_CD, 'CMS0', 0, 1) AS CMS1CNT
          FROM TB_VMS_STTS_HS 
         WHERE RGST_DT BETWEEN '20140620000000' AND '20140620235959' 
       )
UNION 
SELECT 'CCTV' AS FACTYPE, SUM(CMS0CNT) AS CMS0CNT, SUM(CMS1CNT) AS CMS1CNT
  FROM (SELECT DECODE(CMNC_STTS_CD, 'CMS0', 1, 0) AS CMS0CNT, 
               DECODE(CMNC_STTS_CD, 'CMS0', 0, 1) AS CMS1CNT
          FROM TB_WEB_CMRA_STTS_HS 
         WHERE CRTN_DT BETWEEN '20140620000000' AND '20140620235959' 
       )
       )
 ORDER BY FACTYPE


SELECT 'VMS' FCLT_TYPE, C.VMS_CTLR_ID FCLT_ID, B.RGST_DT RGST_DT, A.CONN_STTS_CD, C.VMS_NM FCLT_NAME
  FROM TB_VMS_STTS_HS A, 
       (SELECT VMS_CTLR_NMBR, RGST_DT 
          FROM TB_VMS_STTS_HS 
         WHERE RGST_DT BETWEEN '20140620000000' AND '20140620235959'  
       ) B, 
       TB_VMS_CTLR C 
 WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR 
   AND A.RGST_DT       = B.RGST_DT 
   AND A.VMS_CTLR_NMBR = C.VMS_CTLR_NMBR 
   AND CONN_STTS_CD    = 'CMS1' 
UNION 
SELECT 'CCTV' FCLT_TYPE, C.WEB_CMRA_ID FCLT_ID, B.CRTN_DT RGST_DT, A.CMNC_STTS_CD CONN_STTS_CD, C.ISTL_LCTN_NM FCLT_NAME
  FROM TB_WEB_CMRA_STTS_HS A, 
       (SELECT WEB_CMRA_NMBR, CRTN_DT 
          FROM TB_WEB_CMRA_STTS_HS 
         WHERE CRTN_DT BETWEEN '20140620000000' AND '20140620235959' 
       ) B, 
       TB_WEB_CMRA C 
 WHERE A.WEB_CMRA_NMBR = B.WEB_CMRA_NMBR 
   AND A.CRTN_DT       = B.CRTN_DT 
   AND A.WEB_CMRA_NMBR = C.WEB_CMRA_NMBR 
   AND A.CMNC_STTS_CD  = 'CMS1'