-
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.
-
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
-
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.
-
I think you'd better post some more examples.
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|