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: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> 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).
>
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mgogala_at_adelphia.net
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 Sat Nov 01 2003 - 09:29:25 CST
