원래 안그러던 데이타가 갑자기 그러는건 왜 그럴까요?
쿼리문은 기존에 사용되던 쿼리인데 문제가 뭘까요?
문제는 데이타가 없는 년도를 돌려도 마찬가지로 3분넘게 걸립니다.
SELECT
SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR01
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '01'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR01RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR02
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '02'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR02RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR03
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '03'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR03RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR04
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '04'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR04RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR05
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '05'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR05RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR06
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '06'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR06RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR07
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '07'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR07RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR08
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '08'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR08RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR09
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '09'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR09RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR10
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '10'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR10RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR11
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '11'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR11RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12'
AND TEST_ITEM IN ('S', 'H'))
THEN 1 ELSE 0 END) AS SAR12
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '12'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'SAR'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS SAR12RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC01
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '01'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC01RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC02
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '02'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC02RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC03
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '03'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC03RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC04
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '04'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC04RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC05
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '05'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC05RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC06
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '06'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC06RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC07
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '07'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC07RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC08
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '08'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC08RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC09
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '09'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC09RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC10
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '10'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC10RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC11
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '11'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC11RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12'
AND TEST_ITEM NOT IN ('S', 'H'))
THEN 1 ELSE 0 END) AS EMC12
,(SELECT COUNT(1)
FROM
TB_REGULAR_TESTITEM TRT
,TB_REGULAR_MASTER TRM
,TB_EVENT_SCHEDULE TES
WHERE 1=1
AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
= SUBSTR('2015-01-01', 1, 4) || '12'
AND TRT.REG_NO LIKE 'PM%'
AND TRT.LAB_ID = 'EMC'
AND TRT.REG_NO = TRM.REG_NO
AND TRT.TEST_ITEM = TES.TEST_ITEM
AND TRM.MODEL IN TES.MODEL
AND TRM.OPERATION_FACTORY = TES.OPERATION_FACTORY
AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
= TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
AND TES.EVENT = 'M'
AND TES.EVENT_TYPE = 'P'
AND (SUBSTR(TES.REMARK, 1, 6) = '계획' OR
SUBSTR(TES.REMARK, 1, 12) = '신규추가')
AND SUBSTR(TES.REMARK, 1, 12) != '계획삭제') AS EMC12RESULT
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01')
THEN 1 ELSE 0 END) AS TOTAL01
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02')
THEN 1 ELSE 0 END) AS TOTAL02
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03')
THEN 1 ELSE 0 END) AS TOTAL03
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04')
THEN 1 ELSE 0 END) AS TOTAL04
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05')
THEN 1 ELSE 0 END) AS TOTAL05
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06')
THEN 1 ELSE 0 END) AS TOTAL06
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07')
THEN 1 ELSE 0 END) AS TOTAL07
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08')
THEN 1 ELSE 0 END) AS TOTAL08
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09')
THEN 1 ELSE 0 END) AS TOTAL09
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10')
THEN 1 ELSE 0 END) AS TOTAL10
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11')
THEN 1 ELSE 0 END) AS TOTAL11
,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12')
THEN 1 ELSE 0 END) AS TOTAL12
FROM TB_EVENT_SCHEDULE S
WHERE 1=1
AND S.OPERATION_FACTORY = 'KO_KR'
AND TO_CHAR(S.SCHEDULE_DATE,'YYYY') = SUBSTR('2015-01-01', 1, 4)
AND S.EVENT = 'M'
AND S.EVENT_TYPE = 'P'
AND (SUBSTR(S.REMARK, 1, 6) = '계획' OR
SUBSTR(S.REMARK, 1, 12) = '신규추가')
AND SUBSTR(S.REMARK, 1, 12) != '계획삭제' |