날짜를 아래와같이 입력하면 플래그에 따라서 결과가 그 밑에 처럼 나오게함
플래그가 N이면 날짜는 그대로
Y이면 그 다음에 N을 만날때까지의 날짜
인풋 :
날짜 플래그
20160101 N
20160102 Y
20160103 Y
20160104 Y
20160105 Y
20160106 N
20160107 N
결과값 :
날짜 플래그 날짜(결과)
20160101 N 20160101
20160102 Y 20160106
20160103 Y 20160106
20160104 Y 20160106
20160105 Y 20160106
20160106 N 20160106
20160107 N 20160107
with t as ( select '20160101' dt, 'N' flg from dual union all select '20160102' dt, 'Y' flg from dual union all select '20160103' dt, 'Y' flg from dual union all select '20160104' dt, 'Y' flg from dual union all select '20160105' dt, 'Y' flg from dual union all select '20160106' dt, 'N' flg from dual union all select '20160107' dt, 'N' flg from dual ) select dt, flg, decode(flg,'N',dt ,min(decode(flg,'N',dt,null)) over(order by dt rows between 1 following and unbounded following)) nxt from t