Oracle SQL - Populate Missing dates and populate last available date values for a given ID

Question

Current Data set

Expected Result - Looking for oracle SQL script that will help me to Populate missing dates and carry/roll/copy IDs and Values for given ID.

`
Current Dataset- Has dates, ID and Values

Date    | ID | Value
--------------------
2/4/17  | 3  |  4.4
2/4/17  | 9  |  6.2
2/9/17  | 3  |  4.7
2/9/17  | 4  |  7.4
2/9/17  | 9  |  9.4
2/11/17 | 3  |  9.7
2/11/17 | 7  |  12.4

`Expected Result - Looking for oracle SQL script that will help me to Populate missing dates and carry/roll/copy IDs and Values for given ID.

Expected Result

Date    | ID | Value
--------------------
2/4/17  | 3  |  4.4
2/4/17  | 9  |  6.2
2/5/17  | 3  |  4.4
2/5/17  | 9  |  6.2
2/6/17  | 3  |  4.4
2/6/17  | 9  |  6.2
2/7/17  | 3  |  4.4
2/7/17  | 9  |  6.2
2/8/17  | 3  |  4.4
2/8/17  | 9  |  6.2
2/9/17  | 3  |  4.7
2/9/17  | 4  |  7.4
2/9/17  | 9  |  9.4
2/10/17 | 3  |  4.7
2/10/17 | 4  |  7.4
2/10/17 | 9  |  9.4
2/11/17 | 3  |  9.7
2/11/17 | 7  |  12.4

@LarsGendner The idea is the ID's will be added and subtracted. ID's are unique. Technically I'm naming it as ID because it's a unique value across the column. — war10ck
– war10ck, Commented Mar 28, 2018 at 12:24
@AlexPoole for some reason - I can't seem to add text in this post. Idk why... I added similar post for Qlikview here where you can see the text. stackoverflow.com/questions/49530748/… — war10ck
– war10ck, Commented Mar 28, 2018 at 12:30
@war10ck I do know the concept of what an ID is :-) but I do not get the logic you are aiming at, since in your expected result there are no entries for 2/11/17 for the IDs 4 and 9, and I don't see the reasons why exactly those two just diasppear. — Lars Gendner
– Lars Gendner, Commented Mar 28, 2018 at 13:49

user9566331 · Accepted Answer · 2018-03-28 20:24:52Z

create table test_roll
( dte date,
  id int,
  value number(9,2)
  );

insert into test_roll values(to_date('2/4/17','MM/DD/YYYY'),3, 4.4);
insert into test_roll values(to_date('2/4/17','MM/DD/YYYY'),9, 6.2);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),3, 4.7);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),4, 7.4);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),9, 9.4);
insert into test_roll values(to_date('2/11/17','MM/DD/YYYY'),3, 9.7);
insert into test_roll values(to_date('2/11/17','MM/DD/YYYY'),7, 12.4);

commit;

select * from test_roll order by dte, id;

DTE               ID      VALUE
--------- ---------- ----------
04-FEB-17          3        4.4
04-FEB-17          9        6.2
09-FEB-17          3        4.7
09-FEB-17          4        7.4
09-FEB-17          9        9.4
11-FEB-17          3        9.7
11-FEB-17          7       12.4

SELECT DTE, ID, LAST_VALUE(value) IGNORE NULLS OVER (PARTITION BY ID ORDER BY ID,DTE) AS value
   FROM (
        WITH DATES AS
        (
            select DISTINCT ID, (MIN_DTE + LEVEL - 1) REAL_DTE
            FROM (
                  select ID, min(DTE) as MIN_DTE, 
                    greatest( max(dte), (select max(dte) - 1 from test_roll)) as MAX_DTE
                    FROM test_roll
                  group by ID )
            CONNECT BY LEVEL <= MAX_DTE - MIN_DTE + 1 
        )
        SELECT D.REAL_DTE AS DTE,
               D.ID as ID,
               t.value as value
      FROM DATES D
            LEFT JOIN test_roll t ON t.ID=D.ID AND D.REAL_DTE=t.DTE
       ) ROLLED
       ORDER BY DTE, ID;

DTE               ID      VALUE
--------- ---------- ----------
04-FEB-17          3        4.4
04-FEB-17          9        6.2
05-FEB-17          3        4.4
05-FEB-17          9        6.2
06-FEB-17          3        4.4
06-FEB-17          9        6.2
07-FEB-17          3        4.4
07-FEB-17          9        6.2
08-FEB-17          3        4.4
08-FEB-17          9        6.2
09-FEB-17          3        4.7
09-FEB-17          4        7.4
09-FEB-17          9        9.4
10-FEB-17          3        4.7
10-FEB-17          4        7.4
10-FEB-17          9        9.4
11-FEB-17          3        9.7
11-FEB-17          7       12.4

Collectives™ on Stack Overflow

Oracle SQL - Populate Missing dates and populate last available date values for a given ID

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related