1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | WITH t AS ( SELECT '20130430' dt, 'N' wday, '' hday FROM dual UNION ALL SELECT '20130501' , 'Y' , '' FROM dual UNION ALL SELECT '20130502' , 'N' , '' FROM dual UNION ALL SELECT '20130503' , 'N' , '' FROM dual UNION ALL SELECT '20130504' , 'Y' , '' FROM dual UNION ALL SELECT '20130505' , 'Y' , 'Y' FROM dual UNION ALL SELECT '20130506' , 'N' , '' FROM dual UNION ALL SELECT '20130507' , 'N' , '' FROM dual UNION ALL SELECT '20130508' , 'N' , '' FROM dual UNION ALL SELECT '20130509' , 'N' , '' FROM dual UNION ALL SELECT '20130510' , 'N' , '' FROM dual ) SELECT * FROM ( SELECT dt, wday, hday, dy , CASE WHEN LAG(yn) OVER( ORDER BY dt) = 'Y' AND LEAD(yn) OVER( ORDER BY dt) = 'Y' THEN 'Y' ELSE yn END yn FROM ( SELECT dt, wday, hday , TO_CHAR(TO_DATE(dt, 'yyyymmdd' ), 'dy' ) dy , CASE WHEN wday = 'Y' OR hday = 'Y' OR TO_CHAR(TO_DATE(dt, 'yyyymmdd' ), 'd' ) IN ( '6' , '7' , '1' ) -- 금토일 THEN 'Y' ELSE 'N' END yn FROM t WHERE dt >= TO_CHAR(TO_DATE(:sdt, 'yyyymmdd' )-1, 'yyyymmdd' ) -- 검색시작일 하루전 AND dt <= TO_CHAR(TO_DATE(:edt, 'yyyymmdd' )+1, 'yyyymmdd' ) -- 검색종료일 하루후 ) ) WHERE dt >= :sdt -- 검색시작일 AND dt <= :edt -- 검색종료일 ; |