-- DATA_T 가 DL_MCSERPED_DEV 입니다. WITH T (DT) AS ( SELECT TO_DATE('20180618','YYYYMMDD') + LEVEL -1 FROM DUAL CONNECT BY LEVEL <= SYSDATE - TO_DATE('20180618','YYYYMMDD') + 1 ) , DATE_T (CREATE_DATE , CS_ITEM_PATH_FILE ) AS ( SELECT TO_DATE('20180620','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180620','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180621','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180621','YYYYMMDD') , 'B' FROM DUAL UNION ALL SELECT TO_DATE('20180622','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180623','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180624','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180625','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180626','YYYYMMDD') , 'A' FROM DUAL UNION ALL SELECT TO_DATE('20180627','YYYYMMDD') , 'A' FROM DUAL ) SELECT A.DT , A.BEF_DT , B.CS_ITEM_PATH_FILE FROM (SELECT DT , LAG(DT,1) OVER(ORDER BY DT ) BEF_DT , ROW_NUMBER() OVER(ORDER BY DT ) RN FROM T WHERE TO_CHAR(DT,'D') IN ( '2' , '3' ,'4' , '5' ,'6' ) ) A , DATE_T B , DATE_T C WHERE A.DT = B.CREATE_DATE AND A.BEF_DT = C.CREATE_DATE AND B.CREATE_DATE >= TO_DATE('20180618' , 'YYYYMMDD') AND C.CREATE_DATE >= TO_DATE('20180618' , 'YYYYMMDD') AND B.CS_ITEM_PATH_FILE = C.CS_ITEM_PATH_FILE GROUP BY A.DT , A.BEF_DT , B.CS_ITEM_PATH_FILE HAVING COUNT(*) > 1