Summing Overlapping Date Time Intervals
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Summing Overlapping Date Time Intervals

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Summing Overlapping Date Time Intervals

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2003
    Posts
    6
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think you'd better post some more examples.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As allways in such situations, analytical functions rock!
    Code:
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2003
    Posts
    6
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width