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