CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user3022875
user3022875

Reputation: 9018

Generate Sequence of dates and time

Is it possible to change this query below so that instead of the "OPEN" being the same it would be the LAST "INTERVALENDTIME". Unless of course it is a new day then it would be "OPEN"

For example here are a few lines of date from the current version:

  DT            OPEN                CLOSE          NAME    INTERVAL END
8/4/2015    8/4/2015 9:00:00 AM 8/4/2015 2:00:00 PM Bob 8/4/2015 1:00:00 PM
8/4/2015    8/4/2015 9:00:00 AM 8/4/2015 2:00:00 PM Bob 8/4/2015 2:00:00 PM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 10:00:00 AM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 11:00:00 AM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 12:00:00 PM

and here is what the result should be

  DT            OPEN                CLOSE          NAME    INTERVAL END
8/4/2015   8/4/2015 12:00:00 PM 8/4/2015 2:00:00 PM Bob 8/4/2015 1:00:00 PM
8/4/2015    8/4/2015 1:00:00 PM 8/4/2015 2:00:00 PM Bob 8/4/2015 2:00:00 PM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 10:00:00 AM
8/5/2015   8/5/2015 10:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 11:00:00 AM
8/5/2015   8/5/2015 11:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 12:00:00 PM

here is the query

with par as (
  select date '2015-08-07' enddate, 4 LookBackDays, 3600 inteval, 'Bob' Name, 
         '09:00' open, '14:00' close from dual),
t1 as (
  select to_char(enddate-level+1, 'yyyy-mm-dd') dt, name, open, close from par
    connect by level <= LookBackDays + 1 ),
t2 as (
  select to_char(to_date(open, 'hh24:mi') + (level) * inteval / (24*60*60), 'hh24:mi') tm
    from par
    connect by to_date(open, 'hh24:mi') + level * inteval / (24*60*60) 
               <= to_date(close, 'hh24:mi') )
select to_date(dt, 'yyyy-mm-dd') dt, 
       to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi') open, 
       to_date(dt||' '||close, 'yyyy-mm-dd hh24:mi') close, name, 
       to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi') IntervalEnd
  from t1 cross join t2 order by dt, tm

thank you.

Upvotes: 0

Views: 97

Answers (1)

knuckles
knuckles

Reputation: 101

I think you want to use the lag function and partition by by dt... Hope this helps.

with par as (
  select date '2015-08-07' enddate, 4 LookBackDays, 3600 inteval, 'Bob' Name, 
         '09:00' open, '14:00' close from dual),
t1 as (
  select to_char(enddate-level+1, 'yyyy-mm-dd') dt, name, open, close from par
    connect by level <= LookBackDays + 1 ),
t2 as (
  select to_char(to_date(open, 'hh24:mi') + (level) * inteval / (24*60*60), 'hh24:mi') tm
    from par
    connect by to_date(open, 'hh24:mi') + level * inteval / (24*60*60) 
               <= to_date(close, 'hh24:mi') )
select to_date(dt, 'yyyy-mm-dd') dt, 
       lag(to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi'), 1,to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi'))over(partition by to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi') order by tm) open,  
       to_date(dt||' '||close, 'yyyy-mm-dd hh24:mi') close, name, 
       to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi') IntervalEnd
from t1 cross join t2 order by dt, tm

Upvotes: 1

Related Questions