SQL Connect clause - generate all data by dates

Question

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             VALUE1    VALUE2    
8/1/2017           x         1  
8/1/2017           x         2   
8/7/2017           y         4  
8/7/2017           x         3

Desired output :

DATE             VALUE1     VALUE2  
8/1/2017           x          1  
8/1/2017           x          2  
8/2/2017           x          1  
8/2/2017           x          2

... repeat to 8/6

8/7/2017           y         4  
8/7/2017           x         3  
8/8/2017           y         4  
8/8/2017           x         3

... repeat to sysdate - 1

If you know you need a connect-by clause, how far have you got with the query, and what problems are you having? — Alex Poole, Aug 21 '17 at 18:08
I thin he might just be using connect by to generate arbitrary rows rather than arrange his data hierarchically.. — Caius Jard, Aug 21 '17 at 19:43
By the way, isn't ths some sort of duplicate of https://stackoverflow.com/questions/45743422/oracle-sql-query-generate-records-between-two-dates?rq=1 ? Everything about this question feels like that other one.. — Caius Jard, Aug 21 '17 at 20:05
@CaiusJard - if you read my Comments to the Answer in that thread, you will see that this is in fact not a duplicate. On the contrary, it is a different question (even though it may seem similar and it's from the same poster), so I asked him/her to ask it in a separate (new) thread. — , Aug 21 '17 at 22:12

score 1 · Answer 1 · answered Aug 21 '17 at 22:45

Here is one way to do this. It's not the most elegant or efficient, but it is the most elementary way I could think of (short of really inefficient things like correlated subqueries which can't be unwound easily to joins).

In the first subquery, aliases as a, I create all the needed dates. In the second subquery, b, I create the date ranges, for which we will need to repeat specific rows (in the test data, I allow the number of rows which must be repeated to be variable, to make one of the subtleties of the problem more evident).

With these in hand, it's easy to get the result by joining these two subqueries and the original data. Alas, this approach requires reading the base table three times; hopefully you don't have too much data to process.

with
     inputs ( dt, val1, val2 ) as (
       select date '2017-08-14', 'x', 1 from dual union all
       select date '2017-08-14', 'x', 2 from dual union all
       select date '2017-08-17', 'y', 4 from dual union all
       select date '2017-08-17', 'x', 3 from dual union all
       select date '2017-08-19', 'a', 5 from dual
     )
-- End of simulated inputs (for testing purposes only, not part of the solution).
-- Use your actual table and column names in the SQL query below.
select a.dt, i.val1, i.val2
from   (
         select min_dt + level - 1 as dt
         from   ( select min(dt) as min_dt from inputs )
         connect by level <= sysdate - min_dt
       ) a
       join
       (
         select dt, lead(dt, 1, sysdate) over (order by dt) as lead_dt
         from   (select distinct dt from inputs)
       ) b
         on a.dt >= b.dt and a.dt < b.lead_dt
       join
       inputs i on i.dt = b.dt
order by dt, val1, val2
;

Output:

DT         VAL1 VAL2
---------- ---- ----
2017-08-14 x       1
2017-08-14 x       2
2017-08-15 x       1
2017-08-15 x       2
2017-08-16 x       1
2017-08-16 x       2
2017-08-17 x       3
2017-08-17 y       4
2017-08-18 x       3
2017-08-18 y       4
2017-08-19 a       5
2017-08-20 a       5

Caius Jard · Answer 2 · 2017-08-22T06:48:51.513

You want to make use of the LAST_VALUE analytic function, something like this:

 select 
   fakedate,
   CASE 
     WHEN flip=1 THEN 
       LAST_VALUE(yourvalue1rown1 IGNORE NULLS) OVER(ORDER BY fakedate) 
     ELSE
       LAST_VALUE(yourvalue1rown2 IGNORE NULLS) OVER(ORDER BY fakedate) 
   END as lastvalue1,
   CASE 
     WHEN flip=1 THEN 
       LAST_VALUE(yourvalue2rown1 IGNORE NULLS) OVER(ORDER BY fakedate) 
     ELSE
       LAST_VALUE(yourvalue2rown2 IGNORE NULLS) OVER(ORDER BY fakedate) 
   END as lastvalue2    
 from

 select
   fakedate, flip,
   CASE WHEN rown = 1 THEN yourvalue1 END as yourvalue1rown1,
   CASE WHEN rown = 2 THEN yourvalue1 END as yourvalue1rown2,
   CASE WHEN rown = 1 THEN yourvalue2 END as yourvalue2rown1,
   CASE WHEN rown = 2 THEN yourvalue2 END as yourvalue2rown2
 from
   (select (sysdate - 100) + trunc(rownum/2) fakedate, mod(rownum, 2)+1 as flip from dual connect by level <= 100) fakedates
   left outer join
   (select yt.*, row_number() over(partition by yourdate order by yourvalue1) as rown) yourtable
   on
     fakedate = yourdate and flip = rown

You'll have to adjust the column names to match your table. You'll also have to adjust the 100 to reflect how many days back you need to go to get to the start of your date data.

Please note this is untested (SQLFiddle is having some oracle issues for me at the momnt) so if you get any syntax errors or other minor things you cant fix, comment and I'll address them

This is probably not going to work; there are TWO rows for 8/1 and the values in the two columns must BOTH be replicated for 8/2 and again for 8/3 etc. — , Aug 21 '17 at 22:15
Good point.. I'm now curious to know whether the edited version works out, but sqlfiddle still broken with oracle.. — Caius Jard, Aug 22 '17 at 06:49

SQL Connect clause - generate all data by dates

2 Answers2