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
...................--anoth er 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
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
...................--anoth
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'));
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER