Skip to Main Content
  • Questions
  • Generate missing dates from a given date.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Novice.

Asked: August 15, 2017 - 8:45 pm UTC

Last updated: August 16, 2017 - 2:15 am UTC

Version: 12.1

Viewed 1000+ times

You Asked


I have a table as below:
CREATE TABLE FACT
( AS_OF_DATE DATE NOT NULL,
F_TYPE varchar2(50) ,
F_VALUE NUMBER(23,10)
);

With this data into it:
INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/03/31','yyyy/mm/dd'), 'ABC Corp','1.0');
INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/06/30','yyyy/mm/dd'), 'ABC Corp','1.1');
INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/08/31','yyyy/mm/dd'), 'ABC Corp','1.2');

Required Result set:
As of Date F_TYPE F_VALUE
31-Mar-17 ABC Corp 1.0
30-Apr-17 ABC Corp 1.0
31-May-17 ABC Corp 1.0
30-Jun-17 ABC Corp 1.1
31-Jul-17 ABC Corp 1.1
31-Aug-17 ABC Corp 1.2

Code tried:
SELECT * FROM FACT
UNION
SELECT ADD_MONTHS(AS_OF_DATE,LVL.LVL2) DT,
FACT.F_TYPE,
FACT.F_VALUE
FROM FACT,
(SELECT LEVEL AS LVL2
FROM DUAL
CONNECT BY LEVEL<=2) LVL
,(SELECT MAX(AS_OF_DATE) DT2
FROM FACT) FC2
WHERE ADD_MONTHS(AS_OF_DATE,LVL.LVL2)<=FC2.DT2

This code gives me the below:
As of Date F_TYPE F_VALUE
31-Mar-17 ABC Corp 1.0
30-Apr-17 ABC Corp 1.0
31-May-17 ABC Corp 1.0
30-Jun-17 ABC Corp 1.1
31-Jul-17 ABC Corp 1.1
31-Aug-17 ABC Corp 1.1 (THIS ROW SHOULD'T BE AS PER Business)
31-Aug-17 ABC Corp 1.2


How can i eliminate this row [31-Aug-17 ABC Corp 1.1]

and Connor said...

Thanks for the simple test case.

I've built this up step by step using a WITH clause so you can follow each part of the process. You can probably collapse it down to a smaller SQL if you want to

SQL> CREATE TABLE FACT
  2  ( AS_OF_DATE DATE NOT NULL,
  3  F_TYPE varchar2(50) ,
  4  F_VALUE NUMBER(23,10)
  5  );

Table created.

SQL>
SQL>
SQL> INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/03/31','yyyy/mm/dd'), 'ABC Corp','1.0');

1 row created.

SQL> INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/06/30','yyyy/mm/dd'), 'ABC Corp','1.1');

1 row created.

SQL> INSERT INTO FACT (AS_OF_DATE, F_TYPE,F_VALUE) VALUES (TO_DATE('2017/08/31','yyyy/mm/dd'), 'ABC Corp','1.2');

1 row created.

SQL>
SQL> with
  2  boundaries as
  3  ( select min(as_of_date) lo,
  4           max(as_of_date) hi
  5    from fact ),
  6  all_months as
  7  (  select add_months(lo,rownum-1) mth
  8     from   dual,
  9            boundaries
 10     connect by add_months(lo,rownum-1) <= hi
 11  ),
 12  sparse_data as
 13  ( select *
 14    from   fact f,
 15           all_months a
 16    where  a.mth = f.as_of_date(+)
 17  )
 18  select
 19    mth,
 20    last_value(f_type ignore nulls) over ( order by mth ) as ftype,
 21    last_value(f_value ignore nulls) over ( order by mth ) as f_value
 22  from sparse_data
 23  order by 1;

MTH       FTYPE                                                 F_VALUE
--------- -------------------------------------------------- ----------
31-MAR-17 ABC Corp                                                    1
30-APR-17 ABC Corp                                                    1
31-MAY-17 ABC Corp                                                    1
30-JUN-17 ABC Corp                                                  1.1
31-JUL-17 ABC Corp                                                  1.1
31-AUG-17 ABC Corp                                                  1.2

6 rows selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using LATERAL in 12c

Rajeshwaran, Jeyabal, August 16, 2017 - 12:49 pm UTC

Since this question came from Oracle 12c database, using LATERAL syntax this goes like this.

demo@ORA12C> with datas as (
  2  select min(as_of_date) as start_dt,
  3         max(as_of_date) as end_dt
  4  from fact     )
  5  select x as new_as_of_date,
  6        last_value( f_type ignore nulls ) over (order by x) as f_type,
  7        last_value( f_value ignore nulls ) over (order by x) as f_value
  8  from fact , lateral( select add_months( start_dt ,rownum-1 ) x
  9                       from datas
 10                       connect by add_months( start_dt ,rownum-1 ) <= end_dt )
 11  where fact.as_of_date (+) = x
 12  order by x ;

NEW_AS_OF_D F_TYPE        F_VALUE
----------- ---------- ----------
31-MAR-2017 ABC Corp            1
30-APR-2017 ABC Corp            1
31-MAY-2017 ABC Corp            1
30-JUN-2017 ABC Corp          1.1
31-JUL-2017 ABC Corp          1.1
31-AUG-2017 ABC Corp          1.2

6 rows selected.

demo@ORA12C>

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.