Home » SQL & PL/SQL » SQL & PL/SQL » Merge time duration across data streams based on priority
Merge time duration across data streams based on priority [message #202913] Mon, 13 November 2006 04:54 Go to next message
Messages: 1
Registered: November 2006
Location: Doncaster, England UK
Junior Member
I am looking to solve what to me seems an impossible puzzle. I basically want to allocate time duration to the highest priority stream and any time used within the same period by a lower priority is removed from that stream. A is highest priority. I was wondering if anyone could please help me to solve this. I have been researching Analytics but only just beginning to get to grips with that.

9am 10 11 12 1pm 2 3 4
A --------- --------
B ------------------------- ------------
10:30 1:30
C -------------------- -------

The diagram represents 3 data streams (there are 7 more). Each can generate events that take a period of time during a period from 9am to 5pm. The total 8 hours needs to be allocated across the streams. For example, stream A has an event consuming one hour from 10am to 11 and another from 2 until 3. So the total for stream A would be 2 hours and because this has the highest priority this does not get changed.

I have attached a text file that shows the data layout a bit better.

Now looking at stream B. I need to subtract the time between 10 and 11 and the time from 2 to 2:30 from B. I only subtract from the lower priority the time that the higher priorities overlap. So, I have 9am to 12 giving 3 hours from which I subtract the first A stream hour to leave 2 hours. I then have 1pm to 2:30 giving 1.5 from which I subtract 0.5 hours (the overlap of stream A again) to give 1. The total for stream B is then 2 + 1 = 3 hours.

Stream C requires the same but this time I need to subtract any time from streams A and B that overlap. However this is not the cumulative total just the period.

So, both A and B are have priority at 10:30 and A finishes at 11, however, B continues to 12. This means that C effectively starts at 12 and finishes at 1 which leaves 1 hour to C for the first event. The second event for C is covered completely by stream B and so there is no time allocated to C here. The time in this period is allocated to A and B.

The input data would look something like this

1 B 09:00 12:00
2 A 10:00 11:00
3 C 10:30 13:00
4 B 13:00 14:30
5 C 13:30 14:30
6 A 14:00 15:00

And I want to see

Stream Duration (hours)
A 2
B 3
C 1

Can you help?

Yours sincerely,
  • Attachment: timespan.txt
    (Size: 0.17KB, Downloaded 138 times)
Re: Merge time duration across data streams based on priority [message #202936 is a reply to message #202913] Mon, 13 November 2006 06:39 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this the sort of thing you're looking for:

This query will take each Start - end pair and count the number of hours in that time that are occupied by jobs with a higher stream.

create table temp_stream (id  number, stream  varchar2(2), start_Date  number, end_date number);

insert into temp_stream values (1, 'B' ,09 ,12);
insert into temp_stream values (2, 'A' ,10 ,11);
insert into temp_stream values (3, 'C' ,10 ,13);
insert into temp_stream values (4, 'B' ,13 ,14.5);
insert into temp_stream values (5, 'C' ,13.5 ,14.5);
insert into temp_stream values (6, 'A' ,14 ,15);

select id
      ,(select sum(least(a.end_date,b.end_date) - greatest(b.start_date,a.start_date)) from temp_stream b where b.stream < a.stream and ((b.end_date between a.start_Date and a.end_date) or (b.start_date between a.start_Date and a.end_date))) lost_hours
from   temp_Stream a
order by stream;      

---------- -- ---------- ---------- ----------
         2 A          10         11
         6 A          14         15
         1 B           9         12          1
         4 B          13       14.5         .5
         3 C          10         13          3
         5 C        13.5       14.5        1.5

Am I on the right track?
Previous Topic: explain error 12203
Next Topic: Select case when
Goto Forum:

Current Time: Sat Oct 22 15:37:39 CDT 2016

Total time taken to generate the page: 0.05001 seconds