ITS-OP/SRC/SQL/프로세스 상태 통계.sql

--데이터 입력하고 있는지를 확인해야함.
SELECT A.SYST_ID FCLT_ID, A.SYST_NM FCLT_NM, 
       NVL(B.CNT, 0) CNT_SPS0, NVL(C.CNT, 0) CNT_SPS1, NVL(D.CNT, 0) CNT_SPS2, NVL(E.CNT, 0) CNT_SPS3, NVL(F.CNT, 0) CNT_SPS4 
  FROM TB_UNIT_SYST A, 
       (SELECT SYST_ID, COUNT(SYST_ID) CNT FROM TB_UNIT_SYST_STTS_HS WHERE SYST_STTS_CD = 'SPS0' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY SYST_ID 
       ) B, 
       (SELECT SYST_ID, COUNT(SYST_ID) CNT FROM TB_UNIT_SYST_STTS_HS WHERE SYST_STTS_CD = 'SPS1' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY SYST_ID 
       ) C, 
       (SELECT SYST_ID, COUNT(SYST_ID) CNT FROM TB_UNIT_SYST_STTS_HS WHERE SYST_STTS_CD = 'SPS2' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY SYST_ID 
       ) D, 
       (SELECT SYST_ID, COUNT(SYST_ID) CNT FROM TB_UNIT_SYST_STTS_HS WHERE SYST_STTS_CD = 'SPS3' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY SYST_ID 
       ) E, 
       (SELECT SYST_ID, COUNT(SYST_ID) CNT FROM TB_UNIT_SYST_STTS_HS WHERE SYST_STTS_CD = 'SPS4' AND CRTN_DT BETWEEN '20140619000000' AND '20140619235959' GROUP BY SYST_ID 
       ) F 
 WHERE A.SYST_ID = B.SYST_ID(+) 
   AND A.SYST_ID = C.SYST_ID(+) 
   AND A.SYST_ID = D.SYST_ID(+) 
   AND A.SYST_ID = E.SYST_ID(+) 
   AND A.SYST_ID = F.SYST_ID(+) 
   AND A.SYST_ID IN ('CTV01', 'DSRC01', 'EXT03', 'UOT01', 'UTP01', 'VMS01', 'WALL01', 'WEB01', 'WEB02') 
 ORDER BY A.SYST_ID