WITH test AS
(
SELECT '1' grp , '1' seq , '20070111' dt , 500 amt FROM dual
UNION ALL SELECT '1' , '2' , '20070128' , 1000 FROM dual
UNION ALL SELECT '1' , '3' , '20070215' , 800 FROM dual
UNION ALL SELECT '1' , '4' , '20070222' , 300 FROM dual
UNION ALL SELECT '1' , '5' , '20070301' , 400 FROM dual
UNION ALL SELECT '1' , '6' , '20070305' , 600 FROM dual
UNION ALL SELECT '1' , '7' , '20070310' , 700 FROM dual
UNION ALL SELECT '1' , '8' , '20070319' , 900 FROM dual
UNION ALL SELECT '1' , '9' , '20070401' , 900 FROM dual
UNION ALL SELECT '1' , '10' , '20070505' , 1000 FROM dual
)
SELECT grp
,seq
,dt
,amt
,grp2 소속
,Sum(Decode(rn,1,dt)) over(PARTITION BY grp2) 소속일
from
(SELECT grp
,seq
,dt
,amt
,grp2
,Row_Number() over(PARTITION BY grp2 ORDER BY dt) rn
FROM
(SELECT grp
,seq
,dt
,amt
,Ceil(Decode(To_Date(dt) - To_Date('20070111'),0,1,To_Date(dt) - To_Date('20070111'))/29) grp2
FROM test)
)
결과
1 1 20070111 500 1 20070111
1 2 20070128 1000 1 20070111
1 3 20070215 800 2 20070215
1 4 20070222 300 2 20070215
1 5 20070301 400 2 20070215
1 6 20070305 600 2 20070215
1 7 20070310 700 2 20070215
1 8 20070319 900 3 20070319
1 9 20070401 900 3 20070319
1 10 20070505 1000 4 20070505
