CODEWITHSUNDEEP
sqloracle-databaseconnect-by
user3498646
user3498646

Reputation: 25

SQL Connect clause - generate all data by dates

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

Upvotes: 0

Views: 98

Answers (2)

Caius Jard
Caius Jard

Reputation: 74660

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

Upvotes: 0

user5683823
user5683823

Reputation:

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

Upvotes: 1

Related Questions