Summing Overlapping Date Time Intervals

[bmullin57]

Does anyone know of sql that will sum time of overlapping dates.
eg.
open 01-jan-2003 10:30
close 02-jan-2003 22:30

open 02-jan-2003 11:30
close 03-jan-2003 10:30

Total of 48 hrs. Not 59 hrs.

[slimdave]

It depends just how generalized you want the formula to be.

For two time periods, S1-E1, and S2-E2, where S1 lt E1 and S2 lt E2, and S1 lt S2 and E1 lt E2, then ...

(E2-S1) - Greatest(0,(S2-E1))

... or something very close to it

[bmullin57]

I made it look to simple. I am going to have many overlapping dates and gaps where there are no entries over a 90 day period. I would like to sum the time where there are no entries.

I'll be back on Monday hope someone can help. I will reply then.
Thanks.

[slimdave]

I think you'd better post some more examples.

[jmodic]

As allways in such situations, analytical functions rock!

SQL> desc date_test
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -------
 ID                                                                         NUMBER
 OPEN                                                                       DATE
 CLOSE                                                                      DATE

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi';

Session altered.

SQL> select * from date_test order by open;

 ID OPEN              CLOSE
--- ----------------- -----------------
  1 01-jan-2003 10:30 02-jan-2003 22:30
  2 02-jan-2003 11:30 03-jan-2003 10:30
  3 03-jan-2003 08:00 03-jan-2003 09:00
  4 03-jan-2003 10:00 03-jan-2003 14:00
  5 03-jan-2003 12:00 03-jan-2003 18:00

SQL> alter session set nls_date_format = 'dd-mon hh24:mi';

Session altered.

SQL> select id, open, close,
  2         prev_open, prev_close,
  3         (close - open)*24 as diff,
  4         greatest(0,(close - greatest(open, prev_close)))*24 as nonoverlap_diff
  5  from
  6  (select id, open, close, 
  7          lag(open, 1, open) over (order by open) as prev_open,
  8          lag(close, 1, open) over (order by open) as prev_close
  9   from date_test
 10  )
 11  order by open;

 ID OPEN         CLOSE        PREV_OPEN    PREV_CLOSE    DIFF NONOVERLAP_DIFF
--- ------------ ------------ ------------ ------------ ----- ---------------
  1 01-jan 10:30 02-jan 22:30 01-jan 10:30 01-jan 10:30    36              36
  2 02-jan 11:30 03-jan 10:30 01-jan 10:30 02-jan 22:30    23              12
  3 03-jan 08:00 03-jan 09:00 02-jan 11:30 03-jan 10:30     1               0
  4 03-jan 10:00 03-jan 14:00 03-jan 08:00 03-jan 09:00     4               4
  5 03-jan 12:00 03-jan 18:00 03-jan 10:00 03-jan 14:00     6               4

In the above query/output, column DIFF shows absolute difference between close and open (in hours), while NONOVERLAP_DIFF shows only that part of time difference that was not allready covered by previous interval.

[bmullin57]

Thank you much Jorij I think this will work. There is onsite training I am attending this week. I will continue this work in a few days. I have only used analytical functions on a couple other occasions(yes they rock).