| 123456789101112131415161718192021 |
- --데이터 입력하고 있는지를 확인해야함.
- 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
|
