4 Replies Latest reply: Nov 14, 2012 10:24 AM by user6336927 RSS

    Viewing rolling history of values by day of the month

    user6336927
      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
          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
            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
              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
                Frank and Peter.

                Query works perfectly, thanks for all the assistance.

                Using LAST_VALUE corrected it.

                Phil.