Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
I have a table t1 with 4 cols;
CREATE TABLE t1
(
TV_ID NUMBER,
TARGET_ID NUMBER,
TARGET_START_DATE DATE,
VALUE NUMBER);
Below is a sample output from t1;
TV_ID TARGET_ID TARGET_START_DATE VALUE
----- --------- ----------------- -----
3915 31 01/02/2012 12150
5000 31 15/02/2012 12155
5119 31 21/02/2012 12178
5354 31 25/02/2012 12196
I have a requirement to present the data in the following format;
TARGET_ID TARGET_START_DATE VALUE
----------- ----------------- --------
31 01/02/2012 12150
31 02/02/2012 12150
31 03/02/2012 12150
31 04/02/2012 12150
31 05/02/2012 12150
31 06/02/2012 12150
31 07/02/2012 12150
31 08/02/2012 12150
31 09/02/2012 12150
31 10/02/2012 12150
31 11/02/2012 12150
31 12/02/2012 12150
31 13/02/2012 12150
31 14/02/2012 12150
31 15/02/2012 12155
31 16/02/2012 12155
31 17/02/2012 12155
31 18/02/2012 12155
31 19/02/2012 12155
31 20/02/2012 12155
31 21/02/2012 12178
31 22/02/2012 12178
31 23/02/2012 12178
31 24/02/2012 12178
31 25/02/2012 12196
31 26/02/2012 12196
31 27/02/2012 12196
31 28/02/2012 12196
31 29/02/2012 12196
There was a target set on 01-FEB-2012, then it was adjusted on 15-FEB-2012, 21-FEB-2012 and finally 25-FEB-2012.
We record a target_start_date, but for reporting reasons our business managers want to see a rolling history of the target throughout the month/year.
I've used Feb 2012 in the above example but this same target will roll on into March, April 2012 etc.
This is the code I've written to present the dates chronologically;
SELECT TRUNC (SYSDATE) - rn
FROM ( SELECT ROWNUM rn
FROM DUAL
CONNECT BY LEVEL <= 365)
UNION
SELECT TRUNC (SYSDATE) FROM DUAL;
This looks back one year, but I'm struggling to join this date to table t1 to present the data in my required format.
I've seen the use of the Model clause, WITH clause and global temporary tables, but none quite answer my question.
Any help greatly received.