0

The data in by table is stored by effective date. Can you please help me with an ORACLE SQL statement, that replicates the 8/1 data onto 8/2, 8/3,8/4 and repeat the 8/5 value after?

DATE             VALUE
8/1/2017           x
8/5/2017           b
8/7/2017           a

Desired output : 

DATE             VALUE
8/1/2017           x
8/2/2017           x
8/3/2017           x
8/4/2017           x
8/5/2017           b
8/6/2017           b
user3498646
  • 25
  • 6
  • What dates do you need in the output? Why stop with 8/6 and not with 8/7, which is present in the input (or, for that matter, why not continue through 8/15, copying the value from 8/7)? Don't make up an answer to my question; this should be part of the problem statement, whether it is a production thing or a problem from your textbook or homework. It shouldn't be left for you (or us) to guess. –  Aug 17 '17 at 20:00
  • Possible duplicate of [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates) – Himanshujaggi Aug 18 '17 at 04:58
  • @Himanshujaggi - this question (in this thread) is not only about generating the missing dates, but also about populating the `value` column with the "most recent" existing value from the table. –  Aug 21 '17 at 22:11

1 Answers1

2

Here is one way to do this. It assumes the dates are all pure dates (no time of day component - which in fact means the time of day is 00:00:00 everywhere). It also assumes you want the output to include all the dates between the first and the last date in the inputs.

The first and last dates are computed in the innermost query. Then all the dates between them are created with a hierarchical (connect by) query, and the result is left-joined to the original data. The output is then obtained by using the analytical last_value() function with the ignore nulls option.

with
     inputs ( dt, value ) as (
       select to_date('8/1/2017', 'mm/dd/yyyy'), 'x' from dual union all
       select to_date('8/5/2017', 'mm/dd/yyyy'), 'b' from dual union all
       select to_date('8/7/2017', 'mm/dd/yyyy'), 'a' from dual
     )
-- End of simulated input data (for testing purposes only, not part of the solution).
-- Use your actual table and column names in the SQL query that begins below this line.
select dt, last_value(value ignore nulls) over (order by dt) as value
from   ( select f.dt, i.value
         from   ( select min_dt + level - 1 as dt
                  from   ( select max(dt) as max_dt, min(dt) as min_dt
                           from   inputs
                         )
                  connect by level <= max_dt - min_dt + 1
                ) f
                left outer join inputs i on f.dt = i.dt
       )
;

DT          VALUE
----------  -----
2017-08-01  x
2017-08-02  x
2017-08-03  x
2017-08-04  x
2017-08-05  b
2017-08-06  b
2017-08-07  a
  • Mathguy - How would you extend to multiple columns? In my case, the input date is actually three columns, dt, value1, value2. value1 has a one to many relationship to dt and value2. – user3498646 Aug 21 '17 at 13:07
  • @user3498646 What do you mean? –  Aug 21 '17 at 13:08
  • @user3498646 - that is a different question, unrelated to the original one. I will **edit** your question to restore the original one (which I answered already). If you have a different question, post it in a new thread. The new question is different, because in the old one you didn't have duplicate dates; you just had a few dates, and for each "missing" date you just wanted to copy the most recent value in the VALUE column. This new problem is not even clear yet; what do you want shown for the missing dates, when on existing dates you have multiple rows? –  Aug 21 '17 at 16:18
  • @user3498646 Please note also that such modifications of an original question are against the site rules. Please refrain from such behavior in the future. –  Aug 21 '17 at 16:20