| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Finding overlapping time periods - suggestions please
Mladen,
Unfortunately, the density of your proposed option has warped the space continuum around us and made this option unusable... That and the fact I would have to take two years of University Mathematics to fully comprehend and appreciate the theory, makes this less than optimal in terms of time and space :-)
Another approach that was suggested was to find all distinct intervals (a start time or end time), and then for EACH interval find the number of activities and calculate minutes accordingly.
For example the data below,
1) the distinct intervals are 10:00-11:00, 11:00-11:30, 11:30-12:00, 12:00-13:00, 13:00-13:30, 13:30-16:00. 2) During the 10:00-11:00 (60 min) interval there was ONE task active, find the task and allocate 60 minutes elapsed and single minutes, 0 multiminutes 3) During the 11:00-11:30 interval there were 2 tasks active. Find each and allocate 30 elapsed minutes, 30 multi minutes, 15 prorated mutli minutes. 4) During the 11:30-12:00 intervaal there were 3 tasks active. Find each and allocate 30 elapsed minutes, 30 mutliminutes, 10 prorated mutli minutes.Etc.
Activity----Start---End---Elapsed---Elapsed----Elapsed--Prorated--Prorated ------------Time----Time--Minutes---Mutlitask--Single---Multi-----Minutes ------------------------------------Minutes----Minutes--Minutes 1-----------10:00---12:00--120------060---------60------25--------85 3-----------11:00---13:00--120------120----------0------55--------55 4-----------11:30---13:30--120------090---------30------40--------70 7-----------13:30---16:00--150--------0--------150-------0-------150
Babette
-----Original Message-----
Sent: 2003-11-01 11:59 AM
To: Multiple recipients of list ORACLE-L
Mladen,
If you suggest a convoluted solution like this takes water when you have several million rows I fully agree :-). Funny enough, because it really looks like a purely relational problem, and yet it requires bending backwards. My feeling (and it definitely would deserve time to prove) is that quite possibly it's a design issue - perhaps the proper way would not be to say 'this activitity started then and ended then' but 'at this point in time that activity was running'; in fact, the convoluted part of what I suggest roughly means to do that, changing the design on the fly.
SF
Mladen Gogala wrote:
>
> Stephane, my solution was suggested because the client was a telco which was
> offering each client billing period of their own choosing (weekly, bi-weekly,
> monthly) starting whenever the client wanted. Finding which calls fall in the
> certain period was a major hassle. Of course, the solution like the one that
> I've suggested (and I don't know whether it would really work) would not make
> sense for 3 time periods altogether. What they've ended up implementing was
> a bunch of external procedures based on C and bitmaps, which is, accidentally,
> similar in concept to my solution.
>
> On 2003.11.01 08:09, Stephane Faroult wrote:
> > > babette.turnerunderwood_at_hrdc-drhc.gc.ca wrote:
> > >
> > > I was wondering if anyone had the need to find overlapping time
> > > periods and how to identify them efficiently.
> > >
> > > Here is the scenario:
> > >
> > > Elapsed minutes refer to the actual "clock" time either
> > > spent on a given task. Thus an activity that started at
> > > 9:00 am and finished at 11:00 am on the same day is said to
> > > have 120 elapsed minutes.
> > >
> > > If one task overlaps another (either completely or partially
> > > with another task), then the tasks are said to be
> > > "multitasked". In that case the system will store the
> > > portion of the elapsed time that was multitasked as "elapsed
> > > multitask minutes" and the portion of the time that was not
> > > overlapped as "elapsed single minutes". In addition, for
> > > the portion of time that two or more activities were
> > > simultaneously taking place; their time will be divided by
> > > the number of simultaneous activities and stored as
> > > "prorated multi minutes". The sum of Elapsed Single Minutes
> > > and Prorated Minutes will equal the actual clock time that a
> > > vehicle was active.
> > >
> > > The following example should help to illustrate these
> > > concepts. In the table below a list of fictitious
> > > activities for a vehicle are shown in addition to how the
> > > time is allocated to the various measures:
> > >
> > > Activity Start Time End Time Elapsed Minutes
> > > Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi
> > > Minutes Prorated Minutes
> > > 1 10:00 12:00 120 60 60 25 85
> > > 3 11:00 13:00 120 120 0 55 55
> > > 4 11:30 13:30 120 90 30 40 70
> > > 7 13:30 16:00 150 0 150 0 150
> > > Totals 510 270 240 120 360
> > > The vehicle was active from 10:00 to 16:00, a total of 6 hours (360
> > > minutes) which is equal to the total of Prorated Minutes.
> > >
> > > The vehicle performed 8 ½ hours (510 minutes) of work during
> > > that 6-hour time span. This can be arrived at by adding the
> > > total of Elapsed Multitask Minutes (270) + the total of
> > > Elapsed Single Minutes (240).
> >
> >
> > Babette,
> >
> > I see the problem as quite similar to the 'let's fill up the
> > calendar' problem. Basically the problem is to have time slices and to
> > know what is going on during those slices.
> >
> > It's pretty easy to build up a view returning one row per minute in the
> > timespan which matters; I am using all_tab_columns as a table with more
> > rows than I need, a smarter solution would be the infinite_dual once
> > suggested by Tim Goraman :
> >
> > select y.t0 + rn / 1440 current_time
> > from (select rownum rn
> > from all_tab_columns
> > where rownum < (select (max(end_time) - min(start_time)) * 1440
> > from activities)) x,
> > (select min(start_time) t0
> > from activities) y
> >
> > If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME,
> > END_TIME) is indexed on both START_TIME and END_TIME, it should be fast
> > enough.
> >
> > >From there, it is easy enough to build up a kind of 'bitmap' of
> > activities - this for instance shows a '1' when a given task is active,
> > '0' when it is not :
> >
> > select b.current_time,
> > a.activity,
> > decode(sign(b.current_time - a.start_time),
> > -1, 0,
> > decode(sign(a.end_time - b.current_time), 1, 1,
> > 0))
> > active
> > from activities a,
> > (select y.t0 + rn / 1440 current_time
> > from (select rownum rn
> > from all_tab_columns
> > where rownum <= (select (max(end_time)
> > - min(start_time)) * 1440
> > from activities)) x,
> > (select min(start_time) t0
> > from activities) y) b
> > /
> >
> > a SUM() and a GROUP BY on the current time tell you how many tasks are
> > concurrently active at a given time, etc. Should be enough to get you
> > started ...
> >
> > --
> > Regards,
> >
> > Stephane Faroult
> > Oriole Software
> > --
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <babette.turnerunderwood_at_hrdc-drhc.gc.ca INET: babette.turnerunderwood_at_hrdc-drhc.gc.ca Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Nov 03 2003 - 08:09:25 CST
 |
 |