MAX analytic function with date window preserving time

Question

I'm trying to get the max date over a window of previous rows. Of note is the actual time-portion of the date.

The actual table contains data for plenty more dates, but the query resides in a function which takes a date as an argument to limit the data to one day.

+---------------------------------------------------------------------+
¦FROM  ¦ TO    ¦ GRAPH                                                ¦
+------+-------+------------------------------------------------------¦
¦09:00 ¦ 11:00 ¦  [--------]                                          ¦
¦10:00 ¦ 10:30 ¦     [-]                                              ¦
¦10:45 ¦ 12:00 ¦         [-------]                                    ¦
¦13:00 ¦ 14:30 ¦                      [--------]                      ¦
¦14:00 ¦ 15:00 ¦                            [-----]                   ¦
¦15:30 ¦ 16:30 ¦                                      [----]          ¦
¦16:30 ¦ 17:30 ¦                                           [-----]    ¦
+---------------------------------------------------------------------+

-- show the time portion when querying, for convenience
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI';

-- create an inline view with some data, perform select on it
with iv_dates (start_dt, end_dt) as ( 
select to_date('08/07/2016 09:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 11:00','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 10:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 10:30','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 10:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 12:00','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 13:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 14:30','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 14:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 15:00','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 15:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 16:30','DD/MM/YYYY HH24:MI') from dual 
union all 
select to_date('08/07/2016 16:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 17:30','DD/MM/YYYY HH24:MI') from dual 
) 
 SELECT start_dt 
      , end_dt 
      , MAX (end_dt) OVER ( ORDER BY start_dt asc 
                            RANGE BETWEEN UNBOUNDED PRECEDING 
                            AND     1     PRECEDING 
                          )  
                          max_end_dt
 FROM iv_dates;

However, I'm not getting any result for the max end_dt...

+--------------------------------------------------+
¦ START_DT         ¦ END_DT           ¦ MAX_END_DT ¦
+------------------+------------------+------------¦
¦ 08/07/2016 09:00 ¦ 08/07/2016 11:00 ¦ -          ¦
¦ 08/07/2016 10:00 ¦ 08/07/2016 10:30 ¦ -          ¦
¦ 08/07/2016 10:30 ¦ 08/07/2016 12:00 ¦ -          ¦
¦ 08/07/2016 13:00 ¦ 08/07/2016 14:30 ¦ -          ¦
¦ 08/07/2016 14:00 ¦ 08/07/2016 15:00 ¦ -          ¦
¦ 08/07/2016 15:30 ¦ 08/07/2016 16:30 ¦ -          ¦
¦ 08/07/2016 16:30 ¦ 08/07/2016 17:30 ¦ -          ¦
+--------------------------------------------------+

Though as soon as I add a date for another day it'll start "working"

+--------------------------------------------------------+
¦ START_DT         ¦ END_DT           ¦ MAX_END_DT       ¦
+------------------+------------------+------------------¦
¦ 08/07/2016 09:00 ¦ 08/07/2016 11:00 ¦ -                ¦
¦ 08/07/2016 10:00 ¦ 08/07/2016 10:30 ¦ -                ¦
¦ 08/07/2016 10:30 ¦ 08/07/2016 12:00 ¦ -                ¦
¦ 08/07/2016 13:00 ¦ 08/07/2016 14:30 ¦ -                ¦
¦ 08/07/2016 14:00 ¦ 08/07/2016 15:00 ¦ -                ¦
¦ 08/07/2016 15:30 ¦ 08/07/2016 16:30 ¦ -                ¦
¦ 08/07/2016 16:30 ¦ 08/07/2016 17:30 ¦ -                ¦
¦ 09/07/2016 09:00 ¦ 09/07/2016 11:00 ¦ 08/07/2016 11:00 ¦
¦ 09/07/2016 10:00 ¦ 09/07/2016 10:30 ¦ 08/07/2016 11:00 ¦
¦ 09/07/2016 10:30 ¦ 09/07/2016 12:00 ¦ 08/07/2016 12:00 ¦
+--------------------------------------------------------+

Just to illustrate that though - I don't need partitioning, I just want this to work on my data for one day.

But it seems as though the windowing clause is behaving differently because of the date datatype. I don't want that - I just want it to work on the actual previous row according to the sorting, not on (date - 1). Is it because it is a date, and how could I deal with this?

Answer 1

If you are using ORDER BY start_dt asc range BETWEEN UNBOUNDED PRECEDING 1 PRECEDING Oracle calculate logical offset from current row. Example. For first row from your example, range is 08/07/2016 09:00 and 07/07/2016 09:00 because (start_dt - 1) = 08/07/2016 09:00 -1 = 07/07/2016 09:00. . Change range to rows