This discussion is archived
4 Replies Latest reply: Nov 14, 2012 8:24 AM by user6336927 RSS

Viewing rolling history of values by day of the month

user6336927 Newbie
Currently Being Moderated
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.
  • 1. Re: Viewing rolling history of values by day of the month
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    Try this:
    with sample_data as
    (
    select 3915 TV_ID,  31  TARGET_ID, date '2012-02-01' TARGET_START_DATE, 12150 sample_VALUE from dual union all
    select 5000,     31,          date '2012-02-15',          12155 from dual union all
    select 5119,     31,          date '2012-02-21',          12178 from dual union all
    select 5354,     31,          date '2012-02-25',          12196 from dual 
    
    )
    ,all_days as
    (
    SELECT
      TRUNC (SYSDATE -level + 1) report_date
    FROM
      DUAL
    CONNECT BY
      LEVEL <= 366
    )
    select
      nvl(sd.target_id, lag(sd.target_id,1 ) ignore nulls over (order by  ad.report_date) ) target_id
      ,ad.report_date
      ,nvl(sd.sample_value, lag(sd.sample_value,1 ) ignore nulls over (order by  ad.report_date) ) sample_value
      
    from
      all_days          ad    left outer join
      sample_data       sd    on (ad.report_date = sd.target_start_date)
    
    order by
      ad.report_date
    ;
    
    TARGET_ID REPORT_DATE SAMPLE_VALUE
    --------- ----------- ------------
              15-NOV-11                
              16-NOV-11                
              17-NOV-11                
    ...
              31-JAN-12                
           31 01-FEB-12          12150 
           31 02-FEB-12          12150 
           31 03-FEB-12          12150 
           31 04-FEB-12          12150 
           31 05-FEB-12          12150 
           31 06-FEB-12          12150 
           31 07-FEB-12          12150 
           31 08-FEB-12          12150 
           31 09-FEB-12          12150 
           31 10-FEB-12          12150 
           31 11-FEB-12          12150 
           31 12-FEB-12          12150 
           31 13-FEB-12          12150 
           31 14-FEB-12          12150 
           31 15-FEB-12          12155 
           31 16-FEB-12          12155 
           31 17-FEB-12          12155 
           31 18-FEB-12          12155 
           31 19-FEB-12          12155 
           31 20-FEB-12          12155 
           31 21-FEB-12          12178 
           31 22-FEB-12          12178 
           31 23-FEB-12          12178 
           31 24-FEB-12          12178 
           31 25-FEB-12          12196 
           31 26-FEB-12          12196 
           31 27-FEB-12          12196 
           31 28-FEB-12          12196 
           31 29-FEB-12          12196 
           31 01-MAR-12          12196 
           31 02-MAR-12          12196 
    ...
           31 14-NOV-12          12196 
    
     366 rows selected 
    Regards,

    Peter
  • 2. Re: Viewing rolling history of values by day of the month
    user6336927 Newbie
    Currently Being Moderated
    Thanks for the reply Peter. When I run your example I get;

    ORA-30484: missing window specification for this function

    If I remove the IGNORE NULLS the query runs, but the output isn't quite right, see below;
    TARGET_ID   TARGET_START_DATE   VALUE
    ---------   -----------------   ------
    31         01/02/2012             12150
    31         02/02/2012             12150
                03/02/2012     
                04/02/2012     
                05/02/2012     
                06/02/2012     
                07/02/2012     
                08/02/2012     
                09/02/2012     
                10/02/2012     
                11/02/2012     
                12/02/2012     
                13/02/2012     
                14/02/2012     
    31         15/02/2012             12155
    31         16/02/2012             12155
                17/02/2012     
                18/02/2012     
                19/02/2012     
                20/02/2012     
    31         21/02/2012             12178
    31         22/02/2012             12178
                23/02/2012     
                24/02/2012     
    31         25/02/2012             12196
    31         26/02/2012             12196
                27/02/2012     
                28/02/2012     
                29/02/2012     
    By not ignoring the NULLs I only get a target for the actual target date and one day later?

    Any ideas?

    Phil
  • 3. Re: Viewing rolling history of values by day of the month
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Phil,
    user6336927 wrote:
    Thanks for the reply Peter. When I run your example I get;

    ORA-30484: missing window specification for this function

    If I remove the IGNORE NULLS the query runs, but the output isn't quite right, ...
    No, you need IGNORE NULLS, but in your version of Oracle, IGNORE NULLS doesn't work with LAG. It does work with LAST_VALUE, though.

    Try using LAST_VALUE instead of LAG, and put IGNORE NULLS before the right ')', like this:
    ...
    select
      last_value(sd.target_id ignore nulls) over (order by  ad.report_date) target_id
      ,ad.report_date
      ,last_value(sd.sample_value ignore nulls) over (order by  ad.report_date) sample_value
    from
    The rest of Peter's query remains the same.

    I would use LAST_VALUE for this job even in Oracle 11.2, because it doesn't require NVL.

    LAG ... IGNORE NULLS is a new feature in Oracle 11.2, which is also the first version that allows you to put IGNORE NULLS either before or after the right ')'.
    FIRST_VALUE ... IGNORE NULLS works in Oracle 10.1 and up.
  • 4. Re: Viewing rolling history of values by day of the month
    user6336927 Newbie
    Currently Being Moderated
    Frank and Peter.

    Query works perfectly, thanks for all the assistance.

    Using LAST_VALUE corrected it.

    Phil.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points