Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

Oracle table partition and join on date question

I have a table like this:
Select * from test3 order by f0,f1,f3;
F0      F1      F2      F3
---------------------------------
A      X      7      6/10/2010
A      X      7      6/11/2010
A      X      17      6/12/2010
A      Y      10      6/11/2010
A      Y      20      6/12/2010
A      Y      20      6/13/2010

Attached the table creation script. This table is created such that the value of F2 is the cumulative (from any row before till the current row) sum of F2 with the partition of (F0, F1). How can I create an output that will take the check the last row in the (F1, F2) partition of the above table and add rows to the O/P with identical last row value but the F3 value added by 1 day in each consecutive entry till sysdate.

The O/P will look like:
A      X      7      6/10/2010
A      X      7      6/11/2010
A      X      17      6/12/2010
A      X      17      6/13/2010
A      X      17      6/14/2010
A      X      17      6/15/2010
A      X      17      6/16/2010
...................--another about 240 records
A      X      17      2/15/2011 -- sysdate
A      Y      10      6/11/2010
A      Y      20      6/12/2010
A      Y      20      6/13/2010
A      Y      20      6/14/2010
A      Y      20      6/15/2010
A      Y      20      6/16/2010
A      Y      20      6/17/2010
.......
A      Y      20      2/15/2011 --sysdate

create table test3(f0 varchar2(100), f1 varchar2(100), f2 number(10), f3 date);
insert into test3 values ('A',  'X', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test3 values ('A',  'X', 7, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test3 values ('A',  'X', 17, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test3 values ('A',  'Y', 10, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test3 values ('A',  'Y', 20, to_date('06/20/2010', 'mm/dd/yyyy'));
insert into test3 values ('A',  'Y', 20, to_date('06/20/2010', 'mm/dd/yyyy'));

Open in new window

SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki
toooki

ASKER

Thank you for the help and suggestions. I am testing the code.